Certain Cells Don't Display Full Network Path?

**crwiseman** · 07-28-2011, 07:05 PM

I have this big old worksheet that links up and adds from four other worksheets. All the cells from 3 of 4 of the worksheets are collected in the master worksheet in a typical cell like: =+'P:\ROAD MAP\2011 Working Roadmaps\[Working RoadMap2011 Carla.xls]WorkingFcst'!AE16

But in one little section of the sheet, all I see in the formula which IS pulling the data from one of the other 4 sheets like the example above, all I see in the cell is:
=+'[Working RoadMap2011Heather.xls]WorkingFcst'!AE17

Why is the second example NOT showing the full file path like the first example? I need to live and die by these numbers and I am not comfortable with these mystery formatting whereby I can't be sure where the data is coming from other than seeing that when I change the numbers in the source file, I see the changes in the target file?

**shg** · 07-28-2011, 07:09 PM

When the workbook is open, you won't see the path in the formula, because the formula is referencing the open workbook, not the workbook on disk.

**crwiseman** · 07-28-2011, 07:30 PM

Wow, you are so right. I wonder why that is set-up that way? What value does that add? I would rather have all of the linkages always displayed in full when I am verifying the integrity of a worksheet.

Thanks!

**shg** · 07-28-2011, 07:31 PM

Because if you save the linked workbook as a different name, the links follow the newly saved workbook.

**crwiseman** · 07-28-2011, 07:45 PM

But of what value is that compared with the ability to always see the full file path when verifying accuracy of cells? When I am adding up and verifying worksheets, I don't need any mysterious file paths info disappearing on me IMHO.

**shg** · 07-28-2011, 07:46 PM

Then close the linked workbook.

That's the way Excel works, and for good reason.

**crwiseman** · 07-28-2011, 07:48 PM

But explain what is the good reason? What does this do for you?

**shg** · 07-28-2011, 07:54 PM

As I explained, it tells you exactly where the formulas are linking to: an open workbook. If you make changes to the open (linked) workbook, the values in the linking workbook change (as you would expect), even if the linked workbook changes have not been saved to disk.