Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drop down lists that auto create and then filter the next drop down list | Excel Worksheet Functions | |||
how do I use one drop-list to modify another drop-lists options? | Excel Discussion (Misc queries) | |||
drop down lists | Excel Discussion (Misc queries) | |||
Multiple lists with repeated values for dependet drop down lists | Excel Worksheet Functions | |||
Drop-down Lists | Excel Worksheet Functions |