Bizarre Behavior referencing a sheet.

  • Thread starter Thread starter David Robles
  • Start date Start date
D

David Robles

The excel file that I'm working with is called:
performance summary f00-w03
It has multiple sheets, like f00, w00, s00, f01,..., w03
All of the cells' values are based on formulas, like the following in
the formula bar:

='ACE Program Assistant:Documents:reports:performance data:qtrly
workbooks copy415:[f00 performance datac]bio20b'!Y29+'ACE Program
Assistant:Documents:reports:performance data:qtrly workbooks
copy415:[f00 performance datac]bio20c'!Y29

I'm working with Microsoft Excel X MAC version. My machine name is
'ACE Program assistant'. My understanding of all of this is that the
file being referenced is called "qtrly workbooks copy415" sitting in
the folder "performance data" under "reports", etc.

The problem is that under the "reports" folder there is no
"performance data" folder or excel book called "qtrly workbooks
copy415" But somehow the sheet just calculates a value based on that
formula and it prints out 14 in the cell. Could it be that the file is
hidden? Well I copied the offending formula, placed it in another file
in a separate computer diconnected from the network and the darn
formula still works! why isn't the formula complaining that the file
is not there? How can I figure out what's wrong? If I reference
another file say: " ='ACE Program
Assistant:Documents:reports:whatever:[Sheet1]bio20b'!Y29 then it
complains. But not with Copy415. Why?
Somebody please help.

Thanks,

David
 
The filename being referenced is:

f00 performance datac

in the path:

ACE Program Assistant: ... :qtrly workbooks
copy415:

so "qtrly workbooks copy415" is a folder, not a file (though that
distinction is less that accurate on MacOSX, since it's Unix based).

The sheet being referenced is "bio20c", cell Y29.

The reason XL doesn't complain when you moved the file is that
you've told it not to update links automatically, so it keeps the
value that it had when last saved.
 
Thanks J.E.
But, how can I refresh all of the values, so that if the file that it
references does not exist, I should get the !REF. Right now it is
using values that are cached. Even after I get the prompt
The workbook you opened contains automatic links to information
in other workbooks. Do you want to update this workbook with .....?

After I click YES, the same old information is displayed. can I erase
the cache that Excel uses or whatever it is that it pulls information
from?

thanks,
David
 
Back
Top