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

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

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

Janet

"Janet Panighetti" wrote:

> 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
>
>
>
>

Have a look at the INDIRECT function. Should allow you to use the A2
value as a reference name.
Janet Panighetti wrote:
> This is also posted as subject VLOOKUP -- DYNAMIC TABLE_ARRAY VALUE.
>
> Apologies for the duplicate postings. Answer either or both if you like.
>
> Janet
>
> "Janet Panighetti" wrote:
>
> > 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
> >
> >
> >
> >

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

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

*blush*

Thanks!

Janet

"JW" wrote:

> Have a look at the INDIRECT function. Should allow you to use the A2
> value as a reference name.
> Janet Panighetti wrote:
> > This is also posted as subject VLOOKUP -- DYNAMIC TABLE_ARRAY VALUE.
> >
> > Apologies for the duplicate postings. Answer either or both if you like.
> >
> > Janet
> >
> > "Janet Panighetti" wrote:
> >
> > > 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
> > >
> > >
> > >
> > >
>
>