HELP Drop Down Lists

F

Funkydan

Hi All, I was wondering is some one could help me out on using a
dropdown list.

What im trying to acheieve is that one of the work sheets contain
1000's of Items

Columns

A B
C

Ref Discription Unit Cost


Second Worksheet is going to be used to price items.


and the Sheets would contain following columns

A B
C D E

Ref Qty
Discription Unit Cost Total Cost


Under the Columns C i want a Dropdown list which looks up the Items
from the Worksheet with all items with in.

But what i would also like it to do is carry across the Item which is
selected Unit cost in the the column D Next to it.

How do i do this?

Regards

Dan Cawthorne
 
X

xrelanon

You can use a drop-down list but with a lot of items this may not be a good
idea. Use VLOOKUP to take the selected item number from an input cell or from
the Pick List (I am assuming you are using this Data Validation feature) and
return the unit price, description etc. in your 2nd worksheet. This means
your item list 1st column will have to be sorted in ascending order. Specify
an exact lookup to ensure your user does not input an item not in your list
if you will not be using a pick list. Use IFERROR to trap any unwanted
results to return something like "N/A".
 
F

Funkydan

You can use a drop-down list but with a lot of items this may not be a good
idea. Use VLOOKUP to take the selected item number from an input cell or from
the Pick List (I am assuming you are using this Data Validation feature) and
return the unit price, description etc. in your 2nd worksheet. This means
your item list 1st column will have to be sorted in ascending order. Specify
an exact lookup to ensure your user does not input an item not in your list
if you will not be using a pick list. Use IFERROR to trap any unwanted
results to return something like "N/A".

Thank you for the reply,

could you run by a procedure on how i do this, as i dont know what
Vlookup is.

i haven't actually started building the second sheet yet. Just the
first sheets with items
 
X

xrelanon

It is an Excel function. Source Excel Help, they have some very good examples
about how to use it.
 
L

L. Howard Kittle

I think I understand what you are shooting for and Xrelanon seems to have
offered the correct solution as far as I can tell.

If you want, send me a sample workbook with the layout as you will be
working with. Does not need to be 1000's of items, a dozen will get the
sheets set up and you can expand it once the concept is developed.

I suspect the lookup formula will look something like this... if you have an
error because the lookup value does not exist in the lookup array you will
return "Booger", otherwise you will return the correct price or whatever.

=IF(ISERROR(VLOOKUP(A1,F1:G3,2,0)),"Booger",(VLOOKUP(A1,F1:G3,2,0)))

HTH
Regards,
Howard
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top