Excel - VLOOKUP -- DYNAMIC TABLE_ARRAY VALUE

Asked By JanetPanighett on 14-Sep-07 11:54 AM
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




JanetPanighett replied on 14-Sep-07 12:42 PM
Sorry for the multiple postings.  it is hectic around here and I could not tell
if the first one went through.  I apologize.
Don Guillett replied on 14-Sep-07 02:23 PM
try this idea
=IF(ISNUMBER(D18),VLOOKUP($A18,INDIRECT("Data!B1:X1000"),5,0),"")
=IF(ISNUMBER(D18),VLOOKUP($A18,INDIRECT(a1),5,0),"")
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
JanetPanighett replied on 14-Sep-07 02:42 PM
Well, I though I had tried that before and it was not working.

I tried it this time and it worked.


Perhaps I had not saved the workbook before.

Thanks!
Don Guillett replied on 14-Sep-07 02:56 PM
Glad to help

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com