VLOOKUP -- DYNAMIC TABLE_ARRAY VALUE

[Janet Panighetti]

I am trying to build a dataset (table) of values compiled from the contents
of many input worksheets.

I would like to know if there is a way to pass the table_array argument to
vlookup based upon a text value stored in a cell (which represents a named
range) on another worksheet.

For example, the name DW_160 contains the range of the data stored in the
worksheet "BOOK 160", the name DW_185 contans the range of the data stored in
the worksheet "BOOK 185". Each of these sheets contain various products and
their prices. Each of these sheets is in identical format.

I want to build another sheet that compiles this data into a table which I
can then link to microsoft access for querying.

The page I am building let's call PriceData, And I am trying to get a table
that looks like the following:

PriceData:
A B C
1 Price List Product Price
2 DW_160 Widget $120.00
3 DW_160 Gadget $132.00
4 DW_185 Widget $128.00
5 DW_185 Gadget $145.00


In this table, I only enter the Price List name and the Product and want
vlookup to bring back the price. So, I want something like this in the price
field:

Formula in C2:
=vlookup(A2,<value of A1 for the table_array>,4,false)

How can I express <value of A1 for the table_array> into something vlookup
understands?

Thank you,

Janet

 

[Janet Panighetti]

Sorry for the multiple postings. It's hectic around here and I couldn't tell
if the first one went through. I apologize.

 

[Don Guillett]

try this idea
=IF(ISNUMBER(D18),VLOOKUP($A18,INDIRECT("Data!B1:X1000"),5,0),"")
=IF(ISNUMBER(D18),VLOOKUP($A18,INDIRECT(a1),5,0),"")

 

[Janet Panighetti]

Well, I though I had tried that before and it wasn't working.

I tried it this time and it worked.



Perhaps I hadn't saved the workbook before.

Thanks!

 

[Don Guillett]

Glad to help