variable in a vlookup formula

[Pirrbe]

example : =vlookup(A1;[200601]rates!$A1:$B20;2)
Problem : now I have to use the 'replace' function to change 200601 in 200602. Time consuming as this kind of 'replace' is to execute several times on more complexe vlookup formulas.
I tried to replace the 200601 by a variable that contains the YYYYMM (as cell headers). But when I use this in a vlookup formula, the vlookup act as a 'text string' and loss his functionality.
So my question : is there no way to use 'variables' in a vlookup formula ?
(variable is by example cell A2 where A2 contains a YYYYMM)
Excel version : 2003
Regards, Pirrbe

 

[Domenic]

The INDIRECT function can be used...

=VLOOKUP(A1;INDIRECT("'["&A2&"]rates'!A1:B20");2)

Hope this helps!

 

[Pirrbe]

Hi Domenic, your solution with the INDIRECT function works but... only if the second workbook is open ??. When I close the workbook/sheet on which the 'indirect' variable refers to, then I got #REF# errors. When I open that second workbook then all the vlookups are executed and get the correct values. I gonna play around this weekend with that INDIRECT function. It must work . So thank you very much for this suggestion. Of course extra help is still welcome.

 

[Aladin Akyurek]

Hi Domenic, your solution with the INDIRECT function works but... only if the second workbook is open ??. When I close the workbook/sheet on which the 'indirect' variable refers to, then I got #REF# errors. When I open that second workbook then all the vlookups are executed and get the correct values. I gonna play around this weekend with that INDIRECT function. It must work . So thank you very much for this suggestion. Of course extra help is still welcome.

Switch to INDIRECT.EXT if you want to work with closed books... It requires that you dowload and install the morefunc.xll add-in:

=VLOOKUP(A1;INDIRECT.EXT("'["&A2&"]rates'!A1:B20");2)

 

[Pirrbe]

I installed the morefunc.xll and indeed, the 'INDIRECT.EXT' works. But still a problem : if both excel sheets are in same mainfolder (d:\excel) NO problem. The vlookup finds the correct values. But if I move the 'source' sheet into another folder (d:\excel\data) then it doesn'work. Even after popup windows to correct links... So the problem is still there : I still wonder if you realy can put in a variable the combination [path\workbook].....
example : =vlookup(A1:'D:\excel\data\[source.xls]source'!$A1:$B2;2)
So trying to have d:\excel\data\[source.xls] in a variable and putting it in a indirect.ext doesn't work...

 

[Domenic]

Try...

=VLOOKUP(A1;INDIRECT.EXT("'D:\excel\data\["&A2&"]source'!A1:B20");2)

...where A2 contains the filename. Or, if you prefer...

=VLOOKUP(A1;INDIRECT.EXT("'"&A2&"source'!A1:B20");2)

...where A2 contains the path and filename, such as D:\excel\data\[source.xls].

Hope this helps!

 

[Pirrbe]

Aladin & Dominic, tnx for the tips. IT WORKS. Not only refering the path\workbook but also path\workbook\filename in combination with the INDIRECT.EXT to external books works perfect. So thanks again.