How to create absolue path link to 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.
 
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?
Click to expand...

One possibility is the INDIRECT function; perhaps:
=INDIRECT("'C:\DATA-HOME\Investing\Stocks - Prospects\ValuationParameters.xls'!
GrowthRate")

If it's useful, the parameter of INDIRECT can be concatenated from several sub-strings.

A different possibility is to update the links each time the target is copied, by following the steps suggested by the error message you describe.

(I have Excel 2010.)
 
Back
Top