VLookup in other document

[Davwe]

Here is my problem. Please help

I have two documents A and B.

In document A I have a table existing of codes and descriptions
Range A1: B100 Column A contains codes, Column B contains
descriptions


In document B I want to lookup the description for a given code.
I have a cell C10 containing the code and a cell C11 containing a
formula
=VLOOKUP(A1;'K:\Douane\Regressie\[RTScenario.xls]FxP'!$A$1:$B200;2)
Till so far it works !!

But I use this formula many times in my excel sheet and in many Excel
documents, so if Ithe lokation of document A changes, I have to change
all the formula's in all the documents.

So I want to put the lokation in a named field [location]
M2 = 'K:\Douane\Regressie\[RTScenario.xls]FxP'!$A$1:$B200
And change the formula to formula =VLOOKUP(A1;location;2)

However this does not work. The location is not recognised.
What can be wrong ? Are there other solutions for this problem.

Thanks a lot
Dave

 

[Dave Peterson]

I think you'd want to use =indirect() in your formula. But the bad news is that
indirect() won't work with a closed workbook.

But it might even be just as easy to open your workbook (choose not to update
links if you want) and then Edit|Links to change your links to point to a
different workbook.

Here is my problem. Please help

I have two documents A and B.

In document A I have a table existing of codes and descriptions
Range A1: B100 Column A contains codes, Column B contains
descriptions

In document B I want to lookup the description for a given code.
I have a cell C10 containing the code and a cell C11 containing a
formula
=VLOOKUP(A1;'K:\Douane\Regressie\[RTScenario.xls]FxP'!$A$1:$B200;2)
Till so far it works !!

But I use this formula many times in my excel sheet and in many Excel
documents, so if Ithe lokation of document A changes, I have to change
all the formula's in all the documents.

So I want to put the lokation in a named field [location]
M2 = 'K:\Douane\Regressie\[RTScenario.xls]FxP'!$A$1:$B200
And change the formula to formula =VLOOKUP(A1;location;2)

However this does not work. The location is not recognised.
What can be wrong ? Are there other solutions for this problem.

Thanks a lot
Dave

 

[jagstirling]

Might it be something to do with the fact that the cell you are looking
up is fixed ie it has $ characters either side of the cell reference ?

Click on the cell and then in the formaula bar on the top of the
worksheet click the cell that is fixed (ie. has the $ mark in it).

Press function key F4 three times and the cell will become un fixed
(ie. $ will disappear).

Noe you should be able to drag the fomula down.

J.