Vlookup in book which will change.

[Beto]

Hi,
I'm creating a template to search in a price list in a closed book, this
book will change its name every week, so I want to put the name of the
book in a cell and use this name as an argument in a vlookup function.
Is this feasible?

Ex:
A1 -> PriceList0.xls

C10 -> =VLOOKUP(B10,'P:\PRICES\[PriceList0.xls]Products'!$A$2:$B$10,2,0)

Now in C10 I'd like to change the formula to use the value in A1.

Any suggestions?

Regards,

 

[Frank Kabel]

Hi Beto
if your other workbook is OPEN you may try the following
=VLOOKUP(B10,INDIRECT("'[" & A1 & "]Products'!$A$2:$B$10"),2,0)
This won't work if the other workbook is closed.


You may also have a look at the following thread (describing further
alternatives for accessing closed workbooks): http://tinyurl.com/2c62u

 

[L. Howard Kittle]

Hi Beto,

I was able to set that up doing the following. There may a neater way but I
believe this will work for you. An expmple to show the process, you will
need to adapt your cells etc.

A1 = [
A2 = "PriceList" (no quotes)
A3 = 10 (or whatever PlricList number)
A4 = ]
A5 = =A1&A2&A3&A4
C1 = The vlookup value
C2 = A5&"Sheet1!$A$1:$B$5" (with quotes)
F2 = =IF(ISNA(VLOOKUP(C1,INDIRECT(C2),2,0)),"",
VLOOKUP(C1,INDIRECT(C2),2,0))

Two workbooks named and saved as PriceList0 and PriceList10 with the lookup
tables in A1:B5. This is to simulate the workbook name changes.

Change A3 to either 10 or 0 and enter the lookup values in C1.
So if the new workbook is always PriceList and some number, all you have to
do is change the number in A3 for the formula to lookup in the new book

HTH
Regards,
Howard

 

[Beto]

Thanks to both for your implementations and suggestions, I haven't tried
them yet, but I'm sure it will point me in the right direction.

Regards,