Another vlookup question

[Mark Scureman]

I have several 6 column tables on one worksheet, each set up with a distinct name. The tables contain Part Numbers in column 1 and then 5 different prices (depending on style) in the remaining columns. The part numbers are common to all the tables, but the 5 columns of prices are different depending on the size of the part. On another worksheet, I have a drop down list that lists the part numbers, another that lists the different named ranges for the seperate tables and a third for the column number to get the pricing from. I'm trying to write a single vlookup statement that will pull the correct price based on the choices in each of the drop down lists, but it keeps giving me a #N/A value

Ex
This chart is named "rWall2124
h2) Part Number (i2) Price A (j2) Price B (k2) Price
h3) W9 211 230 25
h4) W12 232 252 27
h5) W15 248 272 29

I have other charts where Price A for part number W9 is 247 or 282 or whatever (8 charts total) depending on the size of the part

On the next sheet, I have drop down lists for the Part Number (A16), the Chart Name (B16) and the Price Column (C16). E16 contains the following formula

=VLOOKUP(A16,B16,C16,FALSE

which returns the #N/A value when I select values from the drop down lists

(a16) W15 (b16) rWall2124 (c16)

I feel like I'm close (this is the beginning of a HUGE project), but could use a little boost to get over this hump and get the project really rolling

TI
Mark

 

[Frank Kabel]

Hi
try
=VLOOKUP(A16,INDIRECT(B16),C16,FALSE)

--
Regards
Frank Kabel
Frankfurt, Germany

I have several 6 column tables on one worksheet, each set up with a

distinct name. The tables contain Part Numbers in column 1 and then 5
different prices (depending on style) in the remaining columns. The
part numbers are common to all the tables, but the 5 columns of prices
are different depending on the size of the part. On another worksheet,
I have a drop down list that lists the part numbers, another that lists
the different named ranges for the seperate tables and a third for the
column number to get the pricing from. I'm trying to write a single
vlookup statement that will pull the correct price based on the choices
in each of the drop down lists, but it keeps giving me a #N/A value.

Ex.
This chart is named "rWall2124"
h2) Part Number (i2) Price A (j2) Price B (k2) Price C
h3) W9 211 230 250
h4) W12 232 252 275
h5) W15 248 272 297

I have other charts where Price A for part number W9 is 247 or 282 or

whatever (8 charts total) depending on the size of the part.

=VLOOKUP(A16,B16,C16,FALSE)

which returns the #N/A value when I select values from the drop down lists.

(a16) W15 (b16) rWall2124 (c16) 4

I feel like I'm close (this is the beginning of a HUGE project), but

could use a little boost to get over this hump and get the project
really rolling.

 

[Jason Morin]

When you refer to text strings in other cells that
resemble actual cell/range references (such as "W15" in
cell A16), you have to use INDIRECT to tell Excel to
convert it to an actual reference.

Although I haven't tested it, see if this works:

=VLOOKUP(INDIRECT(A16),INDIRECT(B16),C16,FALSE)

HTH
Jason
Atlanta, GA

-----Original Message-----
I have several 6 column tables on one worksheet, each set

up with a distinct name. The tables contain Part Numbers
in column 1 and then 5 different prices (depending on
style) in the remaining columns. The part numbers are
common to all the tables, but the 5 columns of prices are
different depending on the size of the part. On another
worksheet, I have a drop down list that lists the part
numbers, another that lists the different named ranges for
the seperate tables and a third for the column number to
get the pricing from. I'm trying to write a single
vlookup statement that will pull the correct price based
on the choices in each of the drop down lists, but it
keeps giving me a #N/A value.

Ex.
This chart is named "rWall2124"
h2) Part Number (i2) Price A (j2) Price B (k2) Price C
h3) W9 211 230 250
h4) W12 232 252 275
h5) W15 248 272 297

I have other charts where Price A for part number W9 is

247 or 282 or whatever (8 charts total) depending on the
size of the part.

=VLOOKUP(A16,B16,C16,FALSE)

which returns the #N/A value when I select values from the drop down lists.

(a16) W15 (b16) rWall2124 (c16) 4

I feel like I'm close (this is the beginning of a HUGE

project), but could use a little boost to get over this
hump and get the project really rolling.

 

[Frank Kabel]

Hi
make this
=VLOOKUP(INDIRECT(A16),INDIRECT(B16),C16,FALSE)

 

[Jason Morin]

Oops...in re-reading your post, I see that W15 is a part
number, not a cell reference. So using INDIRECT on the
first argument is not necessary.

Jason

-----Original Message-----
When you refer to text strings in other cells that
resemble actual cell/range references (such as "W15" in
cell A16), you have to use INDIRECT to tell Excel to
convert it to an actual reference.

Although I haven't tested it, see if this works:

=VLOOKUP(INDIRECT(A16),INDIRECT(B16),C16,FALSE)

HTH
Jason
Atlanta, GA

-----Original Message-----
I have several 6 column tables on one worksheet, each

set
up with a distinct name. The tables contain Part Numbers
in column 1 and then 5 different prices (depending on
style) in the remaining columns. The part numbers are
common to all the tables, but the 5 columns of prices are
different depending on the size of the part. On another
worksheet, I have a drop down list that lists the part
numbers, another that lists the different named ranges for
the seperate tables and a third for the column number to
get the pricing from. I'm trying to write a single
vlookup statement that will pull the correct price based
on the choices in each of the drop down lists, but it
keeps giving me a #N/A value.

Ex.
This chart is named "rWall2124"
h2) Part Number (i2) Price A (j2) Price B (k2) Price C
h3) W9 211 230 250
h4) W12 232 252 275
h5) W15 248 272 297

I have other charts where Price A for part number W9 is

247 or 282 or whatever (8 charts total) depending on the
size of the part.

 

[Mark Scureman]

Hi Frank

Actually, you were right on your first post. I only needed the "INDIRECT" on the B16 reference. Thank you so much for your help! Funny that the help files don't even mention the use of INDIRECT with this function, even under the "See Also" list

Thanks again

Mar

----- Frank Kabel wrote: ----

H
make thi
=VLOOKUP(INDIRECT(A16),INDIRECT(B16),C16,FALSE

-
Regard
Frank Kabe
Frankfurt, German

 

[Mark Scureman]

Hi Jason

It's funny that you and Frank suggested the same things, but in the opposite order. Anyway, you were correct on your second suggestion and it appears to be working correctly with the INDIRECT on the B16 argument. Thanks again for your help

Mar

----- Jason Morin wrote: ----

Oops...in re-reading your post, I see that W15 is a part
number, not a cell reference. So using INDIRECT on the
first argument is not necessary

Jaso

 

[Mark Scureman]

Ok, Frank and Jason have been a great help, but now I have one last wrinkle to add. Well, maybe 2 or 3

Wrinkle #1: I now have a 3rd (and will soon have a 4th and 5th) worksheet which has the exact same tables for the exact same part numbers, but has different pricing (for different materials used). I have named the tables with similar table names, but changed the first letter to reflect the different materials. All the table names in my first post started with a lower case "r" (rWall121518, rWall2124, etc). All the table names for the 3rd worksheet will begin with a lower case "l" (lWall121518, lWall2124, etc), the 4th will be with an "e" and the 5th will be with a "c". I've created another drop down list on the first sheet (in cell A13) with the 4 options on it and would like to have that cell's value dictate which sheet (or group of table names) to pull the pricing information from. Since the table names are exactly the same except for the first letter, perhaps there's a simple way to code that...? I want to stick with table names rather than Sheet/Range values so that I can add part numbers later without having to change the code

On a side note, is there a way to directly access the list of table names that Excel keeps rather than having to type them all into cells so that I can populate the data range of the drop down list

Wrinkle #2: If it's possible, I'd like to add another drop down list to the first sheet (in cell B13) which will allow me to select different groups of part numbers (Wall, Base, Corner, etc). This will control the contents of the list of part numbers in A16 in order to keep it down to a managable size. The part numbers in these different groups are dissimilar enough that a simple replacement scheme such as mentioned above will probably not work, however all of the part numbers will be the duplicated on each of the four worksheets

My E16 formula is now as it should be, based on Frank and Jason's suggestion

=VLOOKUP(A16,INDIRECT(B16),C16,FALSE

but I don't know if it can still be used in this new scenario since the lists populating A16 and B16 are the ones I need to have change based upon the selections in A13 and B13. (A13 will control the contents of B16; B13 will control the contents of A16.

In summary - I have 4 types of materials (A13) out of which a plethora of part numbers (A16) can be made in 5 different styles (C16) and several different sizes (B16). These part numbers can be broken down under smallish number of categories (B13) for ease of choice management

The final thing I need to be able to do is compile an invoice from however many line items I need to add - but that can wait for another day if I've already worn out my welcome for today


----- Mark Scureman wrote: ----

I have several 6 column tables on one worksheet, each set up with a distinct name. The tables contain Part Numbers in column 1 and then 5 different prices (depending on style) in the remaining columns. The part numbers are common to all the tables, but the 5 columns of prices are different depending on the size of the part. On another worksheet, I have a drop down list that lists the part numbers, another that lists the different named ranges for the seperate tables and a third for the column number to get the pricing from. I'm trying to write a single vlookup statement that will pull the correct price based on the choices in each of the drop down lists, but it keeps giving me a #N/A value

Ex
This chart is named "rWall2124
h2) Part Number (i2) Price A (j2) Price B (k2) Price
h3) W9 211 230 25
h4) W12 232 252 27
h5) W15 248 272 29

I have other charts where Price A for part number W9 is 247 or 282 or whatever (8 charts total) depending on the size of the part

On the next sheet, I have drop down lists for the Part Number (A16), the Chart Name (B16) and the Price Column (C16). E16 contains the following formula

=VLOOKUP(A16,B16,C16,FALSE)

which returns the #N/A value when I select values from the drop down lists.

(a16) W15 (b16) rWall2124 (c16) 4

I feel like I'm close (this is the beginning of a HUGE project), but could use a little boost to get over this hump and get the project really rolling.

TIA
Mark

 

[Frank Kabel]

Hi
for your first question have a look at the INDIRECT function
second: have a look at
http://www.contextures.com/xlDataVal02.html
for dependent list boxes