Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
drop down list with items that plug in values elsewhere?
Let's see if i can describe this basically.
I've entered in the width, length and depth of a cardboard box on Sheet 1. On Sheet 2 is a list of different item that can be placed in the box, each with corresponding weights and areas. How do I make a drop down list on Sheet 1 with all of the items on Sheet 2 listed on it that will plug in the weights and areas of the items I choose to put in the box? Honestly, the situation is a bit more complicated than that, but an answer to just that should be sufficient to get me headed in the right direction. Thanks :) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
drop down list with items that plug in values elsewhere?
Well Ruler,
I can't find a method that works fully but here are a few pointers for a temporary solution using Range names,VLOOKUP, MAX and Sum Functions. First, Create Dynamic Range names for the tables. e.g. BoxID in column A choose Insert, Name, Define in the name box type BOXID in the Refers To box type or copy =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1) create a table for the boxes Name: Boxes Refers To The full list of names I created is: Area =OFFSET(ItemID,0,4) Boxes =OFFSET(BoxID,,,,6) BoxID =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1) Capacity =OFFSET(ItemID,0,5) Depth =OFFSET(ItemID,0,3) ItemID =OFFSET(Sheet2!$A$2,,,COUNTA(Sheet2!$A:$A)-1) Length =OFFSET(ItemID,0,1) Products =OFFSET(ItemID,,,,6) Width =OFFSET(ItemID,0,2) In H1: to M1 I copied the headers: Item ID Length Width Depth Area Capacity =OFFSET(BoxID,,,,6) in H2:H6 I set data validation, under the data menu set Allow to list and Source to =ItemID Copy these formulas into I2 to M2 =IF(ISERROR(VLOOKUP($H3,Products,2,0)),"",VLOOKUP( $H3,Products,2,0)) =IF(ISERROR(VLOOKUP($H3,Products,3,0)),"",VLOOKUP( $H3,Products,3,0)) =IF(ISERROR(VLOOKUP($H3,Products,4,0)),"",VLOOKUP( $H3,Products,4,0)) =IF(ISERROR(VLOOKUP($H3,Products,5,0)),"",VLOOKUP( $H3,Products,5,0)) =IF(ISERROR(VLOOKUP($H3,Products,6,0)),"",VLOOKUP( $H3,Products,6,0)) In I9 type =MAX(I3:I8) and copy to J9 In K9 use =SUM(K3:K8) and copy accross to M9 In I11 I used Data Validation set to =MAX(I3:I8), this is where you enter a box for the order. In I11 type =VLOOKUP($H11,Boxes,2,0) and copy across to to M11 changing the column offset. Finally, I used a check in I12 =IF(I11=I9,"OK","Fail") and copied this across to M and set conditional formating to Green Font for OK and Red for Fail. This probably not ideal but it is all I could think of. You can see more on Debra Dalgliesh's site www.contextures.com where you can see dependent list boxes among many other goodies. This might be your best bet in the long run. Regards Peter "Ruleroftheblind" wrote: Let's see if i can describe this basically. I've entered in the width, length and depth of a cardboard box on Sheet 1. On Sheet 2 is a list of different item that can be placed in the box, each with corresponding weights and areas. How do I make a drop down list on Sheet 1 with all of the items on Sheet 2 listed on it that will plug in the weights and areas of the items I choose to put in the box? Honestly, the situation is a bit more complicated than that, but an answer to just that should be sufficient to get me headed in the right direction. Thanks :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to make a drop down list of 3 items that input a value in ano | New Users to Excel | |||
Duplication of items from a drop down list | Excel Discussion (Misc queries) | |||
Items in drop-down list alphabetized | Excel Worksheet Functions | |||
How to add and delete items from a list box or drop down box? | Excel Discussion (Misc queries) | |||
Formula to total items from a drop list with values in several col | Excel Discussion (Misc queries) |