Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Drop Down List & Auto Fill
I have a workbook with two sheets pertaining to this issue. I have one sheet
(A) with a keycode, description, unit of sale , and cost. This sheet has multiple listings of products EX. item code Description Unit of Sale Cost 1/4ply 1/4" BC Plywood Sheet $12.50 Sheet (B) A blank version of sheet A with multiple blank fields for filling in various items and quantities from Sheet A. Item Code Material Sale Unit Estim. Unit Cost Quantity of Units (cont'd) Estimated Cost I want it to where the all the item codes on sheet (A) appear on a drop down menu in a column on Sheet (B). Also, once the appropriate code is chosen I want it to auto fill the appropriate information associated with that item code (description, unit of sale, and cost) in that row. I've been playing with validation fields, lists, and have not found a combination that works between sheets and autofills. Any help is appreciated. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Drop Down List & Auto Fill
To use a data validation drop down list in one sheet with the source for
that list being on a different sheet you have to use a defined name as the source. For example, Sheet2 cell A1 has a drop down list. The source for this list is on Sheet1 in the range A1:A10. Give the range Sheet1 A1:A10 a defined name (something like List) then use this name as the source for the drop down. Navigate to Sheet1. Select the range A1:A10 In the name box (that little box immediately above the column A header) type: List Hit ENTER Navigate to Sheet2 Select cell A1 Goto DataValidation Allow: List Source: =List OK Then, to populate cells with info based on the selections from the drop down lists you can use a lookup formula. See these links: http://contextures.com/xlDataVal01.html http://contextures.com/xlFunctions02.html -- Biff Microsoft Excel MVP "smrsunboy" wrote in message ... I have a workbook with two sheets pertaining to this issue. I have one sheet (A) with a keycode, description, unit of sale , and cost. This sheet has multiple listings of products EX. item code Description Unit of Sale Cost 1/4ply 1/4" BC Plywood Sheet $12.50 Sheet (B) A blank version of sheet A with multiple blank fields for filling in various items and quantities from Sheet A. Item Code Material Sale Unit Estim. Unit Cost Quantity of Units (cont'd) Estimated Cost I want it to where the all the item codes on sheet (A) appear on a drop down menu in a column on Sheet (B). Also, once the appropriate code is chosen I want it to auto fill the appropriate information associated with that item code (description, unit of sale, and cost) in that row. I've been playing with validation fields, lists, and have not found a combination that works between sheets and autofills. Any help is appreciated. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Drop Down List & Auto Fill
Apart from discrepancies between columns on the 2 sheets, there is no reason
why validation lists would not work for the lookup part. The big secret about working with two sheets, is that your list containing the codes will have to be named - Insert, Name, Define) Call it Codes if you wish. Where you want to insert the lookup field, click on Data, Validation, select List, and insert "=Codes" without the "". If you now click opn the down arrow, you will see a list of your codes. Say you do this in A4 In adjacent cells, insert vlookup formulae to look up the values you selected. Always start with =IF(A4="","", and then add the lookup specifications, eg =IF(A4="","",VLOOKUP(A4,Sheet1!$A$2:$D$125,2,FALSE )) will get you the description. Number of units you would fill in. Estimated cost will again be a formula, something like =IF(OR(A4="",E4=""),"",E4*D4) In this example, A4 would contain the code, and E4 the quantity to be sold. -- Hth Kassie Kasselman Change xxx to hotmail "smrsunboy" wrote: I have a workbook with two sheets pertaining to this issue. I have one sheet (A) with a keycode, description, unit of sale , and cost. This sheet has multiple listings of products EX. item code Description Unit of Sale Cost 1/4ply 1/4" BC Plywood Sheet $12.50 Sheet (B) A blank version of sheet A with multiple blank fields for filling in various items and quantities from Sheet A. Item Code Material Sale Unit Estim. Unit Cost Quantity of Units (cont'd) Estimated Cost I want it to where the all the item codes on sheet (A) appear on a drop down menu in a column on Sheet (B). Also, once the appropriate code is chosen I want it to auto fill the appropriate information associated with that item code (description, unit of sale, and cost) in that row. I've been playing with validation fields, lists, and have not found a combination that works between sheets and autofills. Any help is appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drop Down Lists and Auto Fill | New Users to Excel | |||
Auto fill drop down stops at second cell | Excel Discussion (Misc queries) | |||
Auto Fill from Drop Down list | Excel Worksheet Functions | |||
Auto Fill Cells, When Choosing From Drop-Down List... | Excel Worksheet Functions | |||
Creating an auto fill or drop down | Excel Discussion (Misc queries) |