Vlookup Help

[rlcohen]

I have created an excel worksheet. The user enters a number and does
the vlookup on another attached sheet. Then I run totals on it. But
if I have no data in the subsequent cells that I am doing a sum on, it
show #N/A.

Any Ideas> I would also like to just have a drop down combo box that
allows me to choose the part numbers already entered (reducing any
chance of error) and populate with the number.

I have attached the worksheet.

Any help is always appreciated.

Attachment filename: p2p box program spread sheet.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=664345

 

[JE McGimpsey]

I won't look at your worksheet, for a variety of reasons, but consider
using a Validation List. See

http://www.contextures.com/xlDataVal01.html

 

[rlcohen]

In looking at this it appears it will let me choose the item, but wha
if I want a cell to display the price of the Apple (in your example).

So, from the drop down, I want to select the apple, and in anothe
field it displays .50. But i have three feilds that I want a total of
So,
Jan Apple .50
Feb Orange .70
March empty
Subtotal 1.2

 

[JE McGimpsey]

Then, assuming "Jan" is in A1, put the validation dropdown in B1, and in
C1, enter

=VLOOKUP(B1, table, 2, FALSE)

where table is a range of cells with products and prices, e.g:


J K

1 Apple 0.50
2 Orange 0.70
3 Kumquat 1.25
4 ...

In fact, if you also name the first column of your list (say, "fruits",
you can use it in the validation dropdown:

Allow: List
Source: =fruits

 

[rlcohen]

OK, I got that... I am subtotaling the $. But if the user does no
choose anything Excel reports #N/A.

This also carries to the grand total.

How can I get rid of the #N/A if the user chooses no value?

Thanks....

 

[JE McGimpsey]

One way:

=IF(B1="","",VLOOKUP(B1, table, 2, FALSE))

 

[rlcohen]

Perfect!!!!!!!

Last question....

I checked my Excel books and it states that the List function can onl
be used on the sale worksheet. Is there a way to set up a separat
worksheet that has the data, ie apples .50, oranges .75 and that b
pulled into mutliple worksheets within the same workbook?

I have names my data "Product"
=IF(E10="","",VLOOKUP(E10,Product,2,FALSE))

Thanks again

 

[JE McGimpsey]

Your books are wrong. If your table is named Product, then in your
validation cell enter

Allow: List
Source: =OFFSET(Product,,,,1)