vlookup -- table_array as reference to named range

[Guest]

Dear geniuses,

I have price sheets coming from several sources with the same format and
products, but containing difference prices.

I am trying to build a dataset (table) from these sources which I could then
link to Access for querying.

Given each price sheet source is a seperate worksheet in a workbook, I have
given each sheet the named range of its pricing rate. For example, RATE_165
represents the pricing sheet which contains the prices for the "165" rate;
RATE_180 represents the pricing sheet which contains the prices for the "180"
rate.

I want to be able to build another sheet called "PricingData" and have it
look something like this:

PricingData:

A B C
1 Price Sheet Product Price
2 RATE_165 Widget $5.98
3 RATE_165 Gadget $10.00
4 RATE_180 Widget $8.45
5 RATE_180 Gadget $14.95

Where the formula in C2 would be something like:

=vlookup(b2,<value of a2 for the table_array>, 5,false)

And would bring back the price from the range "RATE_165" for a "Widget" in
that range.

Is it possible to convert the value in A2 into a reference for the named
range "RATE_165" within the vlookup function?

Thanks in advance!

Janet

 

[Guest]

This is also posted as subject VLOOKUP -- DYNAMIC TABLE_ARRAY VALUE.

Apologies for the duplicate postings. Answer either or both if you like.

Janet

 

[JW]

Have a look at the INDIRECT function. Should allow you to use the A2
value as a reference name.

 

[Guest]

Thanks. I tried indirect but it wasn't working.

I think maybe I hadn't saved the workbook first.

*blush*

Thanks!

Janet