Excel Help Required Please!

[leigull]

Is there a way that I can populate a drop down list, but base the entries on the answer of another cell - i.e. I have a formula that will return anything from 0-20. Depending on what this figure is, I want the choices in the drop to match, so if 5 is the result of the forumla, then the drop down choices are 0,1,2,3,4,5. If 16 is the result then 0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16 are the choices of the drop down.

Can only seem to get it to do two different options by doing data validation and putting in an if forumla for one result or another, I can't work out how to get it to do multiple if's that go beyond that and up to the 20 or 21 options that I need.

It's Excel 2003 so the older version of data validation unfortunately.

Any assistance greatly appreciated

 

[Buzzer]

Yep. Say cell A1 has the value 5 or 10 and A2 will contain the dropdown list. Somewhere else create 2 ranges: D1: D5 (0-5) and E1:E11 (0-11) in your data validation select list and in the formula bar enter =IF($A$1=5,$D$1:$D$6,$E$1:$E$11)

Change A1 to 10 and see if it works. I've done it before with named ranges but am pissed from lunchtime drinking so can't think straight at the moment.

 

[leigull]

Yep. Say cell A1 has the value 5 or 10 and A2 will contain the dropdown list. Somewhere else create 2 ranges: D1: D5 (0-5) and E1:E11 (0-11) in your data validation select list and in the formula bar enter =IF($A$1=5,$D$1:$D$6,$E$1:$E$11)

Change A1 to 10 and see if it works. I've done it before with named ranges but am pissed from lunchtime drinking so can't think straight at the moment.

Thank you Buzzer.

That seems to populate the dropdown with 0-11 for every number, except 5, when it gives the 0-5 data.

If I list out all 20 possible lists, I just need to somehow increase the if formula to do =if(A1=1,D1: D2,IF(A1=2,E1:E3,IF(A2=3,F1:F4.... etc etc up to.... if(A1=20,W1:W21,"")

But the validation won't seem to let me do multiple if's.

Glad you had a good lunch!

 

[Buzzer]

Got a better solution for you based on a vlookup to named ranges so that you can have as many as you like. PM me your email address and I'll email you the solution.

 

[leigull]

Done - thank you

 

[Buzzer]

A more elgant solution for you. Cell A1 contains your variable, Cell B1 contains the dropdown and cell C1:C21 contains numbers 0-20

Your data validation list range is this formula: =INDIRECT("$C$1:$C$"&A2+1)

The +1 is because C1 starts with zero.

 

[Tom Hark Preston Park]

A more elgant solution for you. Cell A1 contains your variable, Cell B1 contains the dropdown and cell C1:C21 contains numbers 0-20

Your data validation list range is this formula: =INDIRECT("$E$1:$E$"&A2+1)

The +1 is because C1 starts with zero.

Proof indeed that NSC can be a force for good as well as evil

 

[leigull]

Has got me out of a right pickle. Spent most the day trying to work a way round doing this. Buzzer, I salute you and your drunken Excel wizardry