links in XL not working

[Kenny]

I just encountered a very strange problem in Excel and thought I would see if anyone else has encountered this problem.

I opened a file I use frequently. It links only within the file and performs a number of calculations. When I opened it this time some of the links were not working. For example, two cells (Cell1 & Cell2) on different sheets linked to the same cell on a third sheet. The cell being pulled (Cell3) was a calculation resulting in a number. The calculation in Cell3 was working properly and displayed the correct number. Cell1 & Cell2 both showed the word "estimate" rather than the number that appeared in Cell3 and all the calculations which used Cell2 no longer worked. Another example, on the same sheet as Cell1 I have Cell4 which references Cell5. Cell5 is a number (1993), but Cell4 showed 0.

I was able fix them by relinking the cells. Wanting someone else to see this, to see if they had a better solution or a reason for this to happen, I saved it under a different name. This also fixed the problem. I was then able to resave it under the original name and everything seems to be working properly.

I guess I will just chalk it up to being another MS bug

[Brad Gile]

Originally Posted by Kenny

I guess I will just chalk it up to being another MS bug

Nah, just harpoon the bloke who fiddled with your file!

Actually, I think linking involves some hairy and sophisticated stuff that you never see. I avoid using it as much as possible and NEVER do I link to a different workbook.

Brad

[Course 4 Escapee]

How do you use links AND avoid using it with another workbook? Isn't that by definition what a link is?

[Brad Gile]

Originally Posted by Four-time Course 4 Loser

How do you use links AND avoid using it with another workbook? Isn't that by definition what a link is?

Nope. Every workbook (=.XLS file) will have one or more worksheets. Different XLS files are different workbooks.

1. Links can be within one workSHEET or between workSHEETS of a common workBOOK.
2. Links can, however, also be established between cells of two different workbooks.

Both cases can be problematic, but the second is generally the more dangerous. I avoid linking by using VBA to grab the data I need from other workbooks or worksheets directly. That way, if there is an error condition, I can track it down quickly and (usually) easily.

Brad

[Bama Gambler]

I guess I don't understand why you would ever need to link within the same workbook. How about using vlookup? Can someone give me a good reason to link within a workbook?

[Cho Da]

I don't understand that either. It really isn't a "link" if it is between two sheets in the same workbook, it is usually just a cell reference. "Links" should be visible in "Edit Links".

From a maintenance point of view, defining names is the best way to go, especially if you anticipate changing the range towhich the name refers. Note that named references can be relative, or even literals.

[Kenny]

Please excuse my poor choice of wording.

Apparently using the word "link" is my own naming convention and not correct Excel terminology. Is there really any difference?

[Kenny]

Originally Posted by Bama Gambler

I guess I don't understand why you would ever need to link within the same workbook. How about using vlookup? Can someone give me a good reason to link within a workbook?

As per my post above, it isn't technically a link, just a direct cell reference. I have always used the term link when referencing between worksheets.