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


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.
 
Ad

Advertisements

L

Leo Bueno

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?
Are you saying that when you move the target workbook, Excel always >
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top