drop down list with items that plug in values elsewhere?

  • Thread starter Thread starter Ruleroftheblind
  • Start date Start date
R

Ruleroftheblind

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 :)
 
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
 
Back
Top