Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Another Data Validation question
Hi,
i have a question if anyone can help me with using data validation. i have a column range ( C6:C30) i want to set it up so it gives me a drop down list with the following content mdf birch ply small glass shelves large glass shelves opal acrylic clear acrylic metal frames ali channel in the next column (e6:e25) the costs £8.50 £14.50 £25.00 £40.00 £33.00 £33.00 £65.00 £22.00 basically, i want to be able to select a material, and the cost alters accordingly in the next column. i would like the information to be added on a separate sheet called material data that i can update. if anyone can help, it would be a great help. thanks, n.s. |
#2
|
|||
|
|||
You can use data validation, and vlookup formulas to do this. There are
instructions in Excel's Help, and he http://www.contextures.com/xlDataVal01.html http://www.contextures.com/xlFunctions02.html and instructions for an order form that uses this technique he http://www.contextures.com/xlOrderForm01.html Nigel wrote: Hi, i have a question if anyone can help me with using data validation. i have a column range ( C6:C30) i want to set it up so it gives me a drop down list with the following content mdf birch ply small glass shelves large glass shelves opal acrylic clear acrylic metal frames ali channel in the next column (e6:e25) the costs £8.50 £14.50 £25.00 £40.00 £33.00 £33.00 £65.00 £22.00 basically, i want to be able to select a material, and the cost alters accordingly in the next column. i would like the information to be added on a separate sheet called material data that i can update. if anyone can help, it would be a great help. thanks, n.s. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#3
|
|||
|
|||
Put the material in column A of sheet2 and the cost in column B.
Then you could use a formula like: =if(a1="","",vlookup(a1,sheet2!a:b,2,false)) to return the cost (if the data|validation cell were A1). Debra Dalgleish has lots of tips about =vlookup() at: http://contextures.com/xlFunctions02.html Nigel wrote: Hi, i have a question if anyone can help me with using data validation. i have a column range ( C6:C30) i want to set it up so it gives me a drop down list with the following content mdf birch ply small glass shelves large glass shelves opal acrylic clear acrylic metal frames ali channel in the next column (e6:e25) the costs £8.50 £14.50 £25.00 £40.00 £33.00 £33.00 £65.00 £22.00 basically, i want to be able to select a material, and the cost alters accordingly in the next column. i would like the information to be added on a separate sheet called material data that i can update. if anyone can help, it would be a great help. thanks, n.s. -- Dave Peterson |
#4
|
|||
|
|||
Nigel,
The way to go about this is to put have your costs in a sheet, say "Materials" in two columns mdf £8.50 birch ply £14.50 small glass shelves £25.00 I'm sure I don't have the right costs with the materials, but you can take care of that. Now on the Material Data sheet, set up Date - Validation - List, and point it to the first column of the Materials sheet. Since Data Validation can't refer to cells outside the sheet, you'll have to assign a name, Material, (Insert - Name - Define) to the first column of your Materials table. Then select the cells in the second sheet, Data - Validation - List, and in the Source box, put = Material (with the equals). Check the "Dropdown" box. Now you can select the materials right in the Material Data sheet, with the dropdown provided by Data Validaion. For the material costs, perhaps in the next column of the Material Data sheet, use: =VLOOKUP(A2,Materials!A2:B65000,2,FALSE) This will look up the cost for each material in sheet Materials. You can add a multiplier for quanties, if needed: =VLOOKUP(A2,Materials!A2:B65000,2,FALSE) * B2 Where B2 is the quantity column. -- Earl Kiosterud www.smokeylake.com "Nigel" wrote in message ... Hi, i have a question if anyone can help me with using data validation. i have a column range ( C6:C30) i want to set it up so it gives me a drop down list with the following content mdf birch ply small glass shelves large glass shelves opal acrylic clear acrylic metal frames ali channel in the next column (e6:e25) the costs £8.50 £14.50 £25.00 £40.00 £33.00 £33.00 £65.00 £22.00 basically, i want to be able to select a material, and the cost alters accordingly in the next column. i would like the information to be added on a separate sheet called material data that i can update. if anyone can help, it would be a great help. thanks, n.s. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data validation question | New Users to Excel | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) | |||
Data validation, cell protection or other method? | Excel Discussion (Misc queries) | |||
Another Exciting Data Table Question!!!! | Charts and Charting in Excel | |||
DATA VALIDATION IN REVERSE #2 (FOR JULIE D.) | Excel Discussion (Misc queries) |