HELP Drop Down Lists
 

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

HELP Drop Down Lists
 

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".
--
Gnothi se auton.


"Funkydan" wrote:

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

HELP Drop Down Lists
 

On 22 Nov, 15:21, xrelanon wrote:
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".
--
Gnothi se auton.

"Funkydan" wrote:
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

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

HELP Drop Down Lists
 

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


"Funkydan" wrote:

On 22 Nov, 15:21, xrelanon wrote:
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".
--
Gnothi se auton.

"Funkydan" wrote:
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

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

HELP Drop Down Lists
 

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",(VLOOK UP(A1,F1:G3,2,0)))

HTH
Regards,
Howard

"Funkydan" wrote in message
...
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