Dynamic data validation in several cells

[SChalaev]

Hi guys,

I've left more information in the excel sheet that i've prepared but basically I want to have 4 dynamic data validation cells, each updating itself based on the choice made in the previous one, if that is possible at all?

Many thanks

Stas

[benishiryo]

hi Stas. lots of questions

1. so if nothing is selected, are they supposed to have default values of 1-4, 5-8, 9-12?

2. i guess E3 is always 1-4? by the way, merged cells are always a bad idea. avoid it if possible

3. using your eg where 3 is selected for Choice Box 1, does Choice Box 2 contain 4-8 or 4-7? you mentioned 4 in each box only, but you said include 4, so i'm confirming.

4. you then say Choice Box 3 is 7-8? not 4 weeks? 7-10?

5. how about Choice Box 4? is it 8-12, disregarding the 4 per box rule?

for starters, you can select from E5:E10. using data validation list, try this formula:
=OFFSET($A$1,MATCH(E3,$A$2:$A$13,0)+1,,MIN(4,MAX($A:$A)-E3))

if choice box 4 is supposed to show 8-12, then use this for E9:
=OFFSET($A$1,MATCH(E7,$A$2:$A$13,0)+1,,MAX($A:$A)-E7)

by the way, your formulas in column F can be reduced greatly by using:
=VLOOKUP(E3,$A$2:$B$13,2,0)

or even without relying on column B:
=SUMIF($A$2:$A$13,"<="&E3)

[SChalaev]

Thanks for your reply benishiryo!

hi Stas. lots of questions

1. so if nothing is selected, are they supposed to have default values of 1-4, 5-8, 9-12?

Yes.

2. i guess E3 is always 1-4? by the way, merged cells are always a bad idea. avoid it if possible

Yes.

3. using your eg where 3 is selected for Choice Box 1, does Choice Box 2 contain 4-8 or 4-7? you mentioned 4 in each box only, but you said include 4, so i'm confirming.

Choice Box 2 would contain 4-7.

4. you then say Choice Box 3 is 7-8? not 4 weeks? 7-10?

You're right - Choice Box 3 would now contain 7-10, and the rest 2 weeks would "spill" into Choice Box 4.

5. how about Choice Box 4? is it 8-12, disregarding the 4 per box rule?

Yes, for the last one we can disregard 4 per box rule.

for starters, you can select from E5:E10. using data validation list, try this formula:
=OFFSET($A$1,MATCH(E3,$A$2:$A$13,0)+1,,MIN(4,MAX($A:$A)-E3))

if choice box 4 is supposed to show 8-12, then use this for E9:
=OFFSET($A$1,MATCH(E7,$A$2:$A$13,0)+1,,MAX($A:$A)-E7)

by the way, your formulas in column F can be reduced greatly by using:
=VLOOKUP(E3,$A$2:$B$13,2,0)

or even without relying on column B:
=SUMIF($A$2:$A$13,"<="&E3)

Many thanks for the useful tips and re: merged cells. In your first OFFSET formula what does "MIN(4,MAX($A:$A)-E3)" mean in plain English?

Stas

[:) Sixthsense :)]

Try this…

In E3 Cell Validation
=$A$2:$A$13

In E5 Cell Validation
=OFFSET($A$1,MATCH(E3,$A$1:$A$13,0),0)

In E7 Cell Validation
=OFFSET($A$1,MATCH(E5,$A$1:$A$13,0),0)

In E9 Cell Validation
=OFFSET($A$1,MATCH(E5,$A$1:$A$13,0)+1,0,ROWS($A$1:$A$13)-MATCH(E5,$A$1:$A$13,0)-1)

In F3 Cell
=SUMIFS($A$2:$A$13,$A$2:$A$13,">0",$A$2:$A$13,"<="&$E3)

Copy the F3 Cell formula to below cells

[benishiryo]

you're very welcome. that portion is for the height of the range. it must either be 4 cells or lesser. it is done actually for the 4th box. since you want the 4th box to take whatever that's left, this is irrelevant. just for you to understand; if box 1 is 4, box 2 is 8, and box 3 is any value from 9-11, box 4 will be lesser than 4. so i'll take the MINimum between 4 & the x value.

i don't know if you would go more than 12, so i ranged up the whole column to find the MAXimum value. in your scenario, 12 is the maximum in Column A. when i copy the formula from E5 downwards to E9, it would become:
=OFFSET($A$1,MATCH(E7,$A$2:$A$13,0)+1,,MIN(4,MAX($A:$A)-E7))

so the x value i have here is 12 (ie. MAX($A:$A)) minus E7 (i.e. 10). i will get 2. the MINimum between 4 & 2 is 2. so my height for box 4 is only 2 rows high. hope that helps.

to correct things a little after your clarification, E5 would be:
=IF(E3="",$A$6:$A$9,OFFSET($A$1,MATCH(E3,$A$2:$A$13,0)+1,,4))

E7:
=IF(E5="",$A$6:$A$9,OFFSET($A$1,MATCH(E5,$A$2:$A$13,0)+1,,4))

E9 can still be used:
=OFFSET($A$1,MATCH(E7,$A$2:$A$13,0)+1,,MAX($A:$A)-E7)

[samba_ravi]

see the attachement

[SChalaev]

Thanks guys!