I have a NameRange to define a price list on worksheet 2.

On worksheet 1, I have a series of pulldown menus in order to select
an item.

I have tried the following formula successfully but since the
NameRange changes depending on what item has been selected, I need to
"grab" the NameRange selected and do a VLOOKUP within that NameRange.

I know this works...
=IF($C9=" "," ",VLOOKUP($E9,Sheet2!$A$9:$B$174,2,0))

But, I want the part "Sheet2!$A$9:$B$174" to take on the name of the
range based on what option is selected in the pulldown menu.

So if in the pulldown menu in D9 I select "Pipes", and then I select
"2 x 1" in the pulldown menu located in E9, I want my formula to look
for "2 x 1" within the NameRange for "Pipes" and pull the price for
that item found in the next column. Should I select "Ducts" instead
of "Pipes", it needs to look within the NameRange I defined for
"Ducts".

I tried unsuccessfully this...
=IF($C9=" "," ",VLOOKUP($E9,INDIRECT(C9),2,0)) where C9 returns the
NameRange.


TIA

>I tried unsuccessfully this...
>=IF($C9=" "," ",VLOOKUP($E9,INDIRECT(C9),2,0))
>where C9 returns the NameRange.

What does unsuccessfully mean? Did you get an error?

Are your named ranges dynamic ranges?


--
Biff
Microsoft Excel MVP


<lukus2005@gmail.com> wrote in message
news:28b09c83-d3d1-4f96-a6be-af34910a3da6@s50g2000hsb.googlegroups.com...
>I have a NameRange to define a price list on worksheet 2.
>
> On worksheet 1, I have a series of pulldown menus in order to select
> an item.
>
> I have tried the following formula successfully but since the
> NameRange changes depending on what item has been selected, I need to
> "grab" the NameRange selected and do a VLOOKUP within that NameRange.
>
> I know this works...
> =IF($C9=" "," ",VLOOKUP($E9,Sheet2!$A$9:$B$174,2,0))
>
> But, I want the part "Sheet2!$A$9:$B$174" to take on the name of the
> range based on what option is selected in the pulldown menu.
>
> So if in the pulldown menu in D9 I select "Pipes", and then I select
> "2 x 1" in the pulldown menu located in E9, I want my formula to look
> for "2 x 1" within the NameRange for "Pipes" and pull the price for
> that item found in the next column. Should I select "Ducts" instead
> of "Pipes", it needs to look within the NameRange I defined for
> "Ducts".
>
> I tried unsuccessfully this...
> =IF($C9=" "," ",VLOOKUP($E9,INDIRECT(C9),2,0)) where C9 returns the
> NameRange.
>
>
> TIA
>

<where C9 returns the NameRange>

So what is in C9? And if it is a name, how is it defined?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

<lukus2005@gmail.com> wrote in message news:28b09c83-d3d1-4f96-a6be-af34910a3da6@s50g2000hsb.googlegroups.com...
|I have a NameRange to define a price list on worksheet 2.
|
| On worksheet 1, I have a series of pulldown menus in order to select
| an item.
|
| I have tried the following formula successfully but since the
| NameRange changes depending on what item has been selected, I need to
| "grab" the NameRange selected and do a VLOOKUP within that NameRange.
|
| I know this works...
| =IF($C9=" "," ",VLOOKUP($E9,Sheet2!$A$9:$B$174,2,0))
|
| But, I want the part "Sheet2!$A$9:$B$174" to take on the name of the
| range based on what option is selected in the pulldown menu.
|
| So if in the pulldown menu in D9 I select "Pipes", and then I select
| "2 x 1" in the pulldown menu located in E9, I want my formula to look
| for "2 x 1" within the NameRange for "Pipes" and pull the price for
| that item found in the next column. Should I select "Ducts" instead
| of "Pipes", it needs to look within the NameRange I defined for
| "Ducts".
|
| I tried unsuccessfully this...
| =IF($C9=" "," ",VLOOKUP($E9,INDIRECT(C9),2,0)) where C9 returns the
| NameRange.
|
|
| TIA
|

I just tested this where c16 was a named vlookup range specified by the data
validation drop down and d16 was the value to lookup also selected by a data
validation drop down. You may have a problem with the list and the value to
look up not being the same 2x1 or quotes or trim.

=VLOOKUP(D16,INDIRECT(C16),2)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
<lukus2005@gmail.com> wrote in message
news:28b09c83-d3d1-4f96-a6be-af34910a3da6@s50g2000hsb.googlegroups.com...
>I have a NameRange to define a price list on worksheet 2.
>
> On worksheet 1, I have a series of pulldown menus in order to select
> an item.
>
> I have tried the following formula successfully but since the
> NameRange changes depending on what item has been selected, I need to
> "grab" the NameRange selected and do a VLOOKUP within that NameRange.
>
> I know this works...
> =IF($C9=" "," ",VLOOKUP($E9,Sheet2!$A$9:$B$174,2,0))
>
> But, I want the part "Sheet2!$A$9:$B$174" to take on the name of the
> range based on what option is selected in the pulldown menu.
>
> So if in the pulldown menu in D9 I select "Pipes", and then I select
> "2 x 1" in the pulldown menu located in E9, I want my formula to look
> for "2 x 1" within the NameRange for "Pipes" and pull the price for
> that item found in the next column. Should I select "Ducts" instead
> of "Pipes", it needs to look within the NameRange I defined for
> "Ducts".
>
> I tried unsuccessfully this...
> =IF($C9=" "," ",VLOOKUP($E9,INDIRECT(C9),2,0)) where C9 returns the
> NameRange.
>
>
> TIA
>

- A9 is a pulldown menu showing different categories of material, for
example: Pipes, Ducts, Jacketing, etc...
- A9 is a Data Validation List defined as follow: =Categories (where
Categories is a NameRange).

- C9 is another pulldown menu showing sub options specific to what was
selected in A9, for example: If Pipes is selected in A9, then C9 will
show these options... Fiberglass, Mineral Fiber, etc...
- C9 is a Data Validation List defined as follow:
=INDIRECT(VLOOKUP(A9,NameLookup,2,0)) (where NameLookup is a 2-column
NameRange so I can remove spaced in my pulldown menu options).

- E9 is the last pulldown menu that allows you to select the size (or
specific item) based on what was selected in C9, for example: 1"x1",
2"x1", etc...
- E9 is a Data Validation List defined as follow: =INDIRECT(C9)

- G9 should show the unit price associated with the item selected in
E9.
- I tried doing this by using the following formula: =IF($C9=" ","
",VLOOKUP($E9,INDIRECT(C9),2,0)) but that produces a #REF! error.
- If I bypass the INDIRECT(C9) function in my formula and hard code
it, it works, for example: =IF($C9=" "," ",VLOOKUP($E9,Piping!$A$9:$B
$174,2,0))

I have created a NameRange for every menu options and price lists.
The price lists contain 2 columns, one for the item name and one for
the unit price. There's a worksheet for ever menu options found in the
A9 pulldown which contains all the price lists for material associated
with that category. But the price list is broken down into sub-
categories (as displayed in C9 pulldown menu).

I hope this details example isn't more confusing than my previous
one. I'm almost there, just need to get that price (G9) to show up.
Thanks once again!


On Jul 7, 4:20 pm, "Niek Otten" <nicol...@xs4all.nl> wrote:
> <where C9 returns the NameRange>
>
> So what is in C9? And if it is a name, how is it defined?
>
> --
> Kind regards,
>
> Niek Otten
> Microsoft MVP - Excel
>
> <lukus2...@gmail.com> wrote in messagenews:28b09c83-d3d1-4f96-a6be-af34910a3da6@s50g2000hsb.googlegroups.com...
>
> |I have a NameRange to define a price list on worksheet 2.
> |
> | On worksheet 1, I have a series of pulldown menus in order to select
> | an item.
> |
> | I have tried the following formula successfully but since the
> | NameRange changes depending on what item has been selected, I need to
> | "grab" the NameRange selected and do a VLOOKUP within that NameRange.
> |
> | I know this works...
> | =IF($C9=" "," ",VLOOKUP($E9,Sheet2!$A$9:$B$174,2,0))
> |
> | But, I want the part "Sheet2!$A$9:$B$174" to take on the name of the
> | range based on what option is selected in the pulldown menu.
> |
> | So if in the pulldown menu in D9 I select "Pipes", and then I select
> | "2 x 1" in the pulldown menu located in E9, I want my formula to look
> | for "2 x 1" within the NameRange for "Pipes" and pull the price for
> | that item found in the next column. Should I select "Ducts" instead
> | of "Pipes", it needs to look within the NameRange I defined for
> | "Ducts".
> |
> | I tried unsuccessfully this...
> | =IF($C9=" "," ",VLOOKUP($E9,INDIRECT(C9),2,0)) where C9 returns the
> | NameRange.
> |
> |
> | TIA
> |

- A9 is a pulldown menu showing different categories of material, for
example: Pipes, Ducts, Jacketing, etc...
- A9 is a Data Validation List defined as follow: =Categories (where
Categories is a NameRange).

- C9 is another pulldown menu showing sub options specific to what was
selected in A9, for example: If Pipes is selected in A9, then C9 will
show these options... Fiberglass, Mineral Fiber, etc...
- C9 is a Data Validation List defined as follow:
=INDIRECT(VLOOKUP(A9,NameLookup,2,0)) (where NameLookup is a 2-column
NameRange so I can remove spaced in my pulldown menu options).

- E9 is the last pulldown menu that allows you to select the size (or
specific item) based on what was selected in C9, for example: 1"x1",
2"x1", etc...
- E9 is a Data Validation List defined as follow: =INDIRECT(C9)

- G9 should show the unit price associated with the item selected in
E9.
- I tried doing this by using the following formula: =IF($C9=" ","
",VLOOKUP($E9,INDIRECT(C9),2,0)) but that produces a #REF! error.
- If I bypass the INDIRECT(C9) function in my formula and hard code
it, it works, for example: =IF($C9=" "," ",VLOOKUP($E9,Piping!$A$9:$B
$174,2,0))

I have created a NameRange for every menu options and price lists.
The price lists contain 2 columns, one for the item name and one for
the unit price. There's a worksheet for ever menu options found in the
A9 pulldown which contains all the price lists for material associated
with that category. But the price list is broken down into sub-
categories (as displayed in C9 pulldown menu).

I hope this details example isn't more confusing than my previous
one. I'm almost there, just need to get that price (G9) to show up.
Thanks once again!

On Jul 7, 4:20 pm, "Niek Otten" <nicol...@xs4all.nl> wrote:
> <where C9 returns the NameRange>
>
> So what is in C9? And if it is a name, how is it defined?
>
> --
> Kind regards,
>
> Niek Otten
> Microsoft MVP - Excel
>
> <lukus2...@gmail.com> wrote in messagenews:28b09c83-d3d1-4f96-a6be-af34910a3da6@s50g2000hsb.googlegroups.com...
>
> |I have a NameRange to define a price list on worksheet 2.
> |
> | On worksheet 1, I have a series of pulldown menus in order to select
> | an item.
> |
> | I have tried the following formula successfully but since the
> | NameRange changes depending on what item has been selected, I need to
> | "grab" the NameRange selected and do a VLOOKUP within that NameRange.
> |
> | I know this works...
> | =IF($C9=" "," ",VLOOKUP($E9,Sheet2!$A$9:$B$174,2,0))
> |
> | But, I want the part "Sheet2!$A$9:$B$174" to take on the name of the
> | range based on what option is selected in the pulldown menu.
> |
> | So if in the pulldown menu in D9 I select "Pipes", and then I select
> | "2 x 1" in the pulldown menu located in E9, I want my formula to look
> | for "2 x 1" within the NameRange for "Pipes" and pull the price for
> | that item found in the next column. Should I select "Ducts" instead
> | of "Pipes", it needs to look within the NameRange I defined for
> | "Ducts".
> |
> | I tried unsuccessfully this...
> | =IF($C9=" "," ",VLOOKUP($E9,INDIRECT(C9),2,0)) where C9 returns the
> | NameRange.
> |
> |
> | TIA
> |

Try not checking for spaces:

=IF($C9=" "," ",VLOOKUP($E9,INDIRECT(C9),2,0))
could be:
=IF($C9="","",VLOOKUP($E9,INDIRECT(C9),2,0))



"lukus2005@gmail.com" wrote:
>
> - A9 is a pulldown menu showing different categories of material, for
> example: Pipes, Ducts, Jacketing, etc...
> - A9 is a Data Validation List defined as follow: =Categories (where
> Categories is a NameRange).
>
> - C9 is another pulldown menu showing sub options specific to what was
> selected in A9, for example: If Pipes is selected in A9, then C9 will
> show these options... Fiberglass, Mineral Fiber, etc...
> - C9 is a Data Validation List defined as follow:
> =INDIRECT(VLOOKUP(A9,NameLookup,2,0)) (where NameLookup is a 2-column
> NameRange so I can remove spaced in my pulldown menu options).
>
> - E9 is the last pulldown menu that allows you to select the size (or
> specific item) based on what was selected in C9, for example: 1"x1",
> 2"x1", etc...
> - E9 is a Data Validation List defined as follow: =INDIRECT(C9)
>
> - G9 should show the unit price associated with the item selected in
> E9.
> - I tried doing this by using the following formula: =IF($C9=" ","
> ",VLOOKUP($E9,INDIRECT(C9),2,0)) but that produces a #REF! error.
> - If I bypass the INDIRECT(C9) function in my formula and hard code
> it, it works, for example: =IF($C9=" "," ",VLOOKUP($E9,Piping!$A$9:$B
> $174,2,0))
>
> I have created a NameRange for every menu options and price lists.
> The price lists contain 2 columns, one for the item name and one for
> the unit price. There's a worksheet for ever menu options found in the
> A9 pulldown which contains all the price lists for material associated
> with that category. But the price list is broken down into sub-
> categories (as displayed in C9 pulldown menu).
>
> I hope this details example isn't more confusing than my previous
> one. I'm almost there, just need to get that price (G9) to show up.
> Thanks once again!
>
> On Jul 7, 4:20 pm, "Niek Otten" <nicol...@xs4all.nl> wrote:
> > <where C9 returns the NameRange>
> >
> > So what is in C9? And if it is a name, how is it defined?
> >
> > --
> > Kind regards,
> >
> > Niek Otten
> > Microsoft MVP - Excel
> >
> > <lukus2...@gmail.com> wrote in messagenews:28b09c83-d3d1-4f96-a6be-af34910a3da6@s50g2000hsb.googlegroups.com...
> >
> > |I have a NameRange to define a price list on worksheet 2.
> > |
> > | On worksheet 1, I have a series of pulldown menus in order to select
> > | an item.
> > |
> > | I have tried the following formula successfully but since the
> > | NameRange changes depending on what item has been selected, I need to
> > | "grab" the NameRange selected and do a VLOOKUP within that NameRange.
> > |
> > | I know this works...
> > | =IF($C9=" "," ",VLOOKUP($E9,Sheet2!$A$9:$B$174,2,0))
> > |
> > | But, I want the part "Sheet2!$A$9:$B$174" to take on the name of the
> > | range based on what option is selected in the pulldown menu.
> > |
> > | So if in the pulldown menu in D9 I select "Pipes", and then I select
> > | "2 x 1" in the pulldown menu located in E9, I want my formula to look
> > | for "2 x 1" within the NameRange for "Pipes" and pull the price for
> > | that item found in the next column. Should I select "Ducts" instead
> > | of "Pipes", it needs to look within the NameRange I defined for
> > | "Ducts".
> > |
> > | I tried unsuccessfully this...
> > | =IF($C9=" "," ",VLOOKUP($E9,INDIRECT(C9),2,0)) where C9 returns the
> > | NameRange.
> > |
> > |
> > | TIA
> > |

--

Dave Peterson