How to create absolute link to cell in another workbook (Excel 2003)

  • Thread starter Thread starter Leo Bueno
  • Start date Start date
L

Leo Bueno

I have a workbook which needs to reference data in another workbook.
Let's call this workbook the target workbook.

Here is the path to a referenced cell.

=('C:\DATA-HOME\Investing\ValuationParameters.xls'!GrowthRate)

Let's call ValuationParameters.xls the source workbook. The location of
of the source (ValationPaameters.xls) is static.

Both workbooks are located in the same subdirectory.

When I open the target workbook, the linked cell value to
ValuationParamenters.xls works just fine (i.e., whatever "GrowthRate"
value is found in the source is inserted in the target).

The problem is that when I save a copy of the target workbook in another
*directory*, the link no longer works.

For example, if I save the target workbook in
C:\DATA-HOME\Investing\Stocks - Prospects\

when I open the target I get an error saying that the links need to be
updated.

The link now looks like this:

=('C:\DATA-HOME\Investing\Stocks - Prospects\ValuationParameters.xls'!
GrowthRate)

How do I create the link references so that no matter where the target
is located the cell reference will contain the absolute path to
ValuationParameters.xls?

Thanks in advance for your help.
 
I'm not sure that I understand your question. Doesn't it work just to
put the original value that Excel assigned - that is,
=('C:\DATA-HOME\Investing\ValuationParameters.xls'!GrowthRate)
as the formula in the cell you want in the target workbook?
Click to expand...
Are you saying that when you move the target workbook, Excel always >
Click to expand...
changes the formula so the path is the same as that of the target?
Yes, precisely.

It looks like the path is relative to the location of the target file.
 
Back
Top