dependent data from dropdown

[rpeas]

easy one im sure, but i cant work it out!

If cell A had a working dropdown menu, how does one make Cell B apply data based on that choice: i.e

if user chooses apple from cell one, the quantity of apple in cell 2 ALWAYS 6
if user chooses orange from cell one, the quantity of orange in cell 2 is 7

is there a technical name for this?

many thanks!

 

[rs2k]

A VLOOKUP will do this.

 

[rpeas]

hi rs2k,

i applied the VLOOKUP command, but it appears not to have worked because the cell it is linked to is not constant. i.e the drop down menu has several variables, so it gives me a REF error.

must be a way around this though. any other ideas?

r.

 

[rs2k]

If you make a list, say Y1=Apple Z1=6, Y2=Orange Z2=7, Y3=Pear Z3=8 etc... you get the picture, then the vlookup will work, so presuming the list is in A1 the formula in B1 is;

=VLOOKUP(A1,Y1:Z3,2,0)

You could always use a ws to store your list and have it hidden,

HTH
Colin

 

[grizz]

lets say G11:G13 = apples oranges pears
H11: H13 = 6 7 8
your drop down in A1 -- in B1 =index(H11:H13,MATCH(A1,G11:G13,0))