Data Validation with nested IF statements or VLOOKUP does not work.

[mikeyank]

Hello, <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Here is my problem: <o></o>
Excel spreadsheet will contain a form that will consist of drop down boxes (data validation). Each drop down box will define the data that can be selected in the next drop down box. <o></o>
<o></o>
The way I initially went about it, is creating nested IF statements. However, we all know there is a limit of 7 nested IF statements. I have 10! So below formula does not work:<o></o>
=if(B5=1,F5:I5, if(B5=2, F6:I6, if(B5=3, F7:H7, if(B5=4, F8:J8, if(B5=5, F9:H9, if(B5=6,F10:G10, if(B5=7,F11:H11, if(B5=8,F12:H12, if(B5=9, F13:I13, if(B5=10, F14:K14))))))))))<o></o>
<o></o>
I tried other workarounds such as CONCATENATE, or & signs. No luck. VLOOKUP does not work also, because there are multiple columns in col_index_num. Anything else I try gives me this message:<o></o>
“The List Source must be a delimited list, or a reference to a single row or column”<o></o>
<o></o>
What should really happen is this:<o></o>
User selects value in first list box. Second list box shows values associated wih the value from the first drop down only. <o></o>
<o></o>
<o>

</o>

 

[dsg1480]

I see someone suggested using the OFFSET formula in the Source of the second drop down validation, but the rest of the formula is very complicated. I made a more dumbed-down version of how to do this, however it doesn't address the issue raised earlier of how to get rid of a value in Data Validation 2 if Data Validation 1 is changed.

I used the OFFSET formula in the Source of the second drop down validation and used the following as the pieces of my OFFSET formula:

=OFFSET($G$4,VLOOKUP($B$5,$D$4:$E$10,2,FALSE),0,1,VLOOKUP($B$5,$D$4:$G$10,3,FALSE))

Reference: $G$4 - This is the top-left corner of the Drop_Down_2 area
Rows: VLOOKUP($B$5,$D$4:$E$10,2,FALSE) - This gives you the Row_Offset value from column E corresponding to the chosen Drop_Down_1 value
Cols: 0 - We don't want to offset our range to the right of column G
[height]: 1 - We only want to return range that has 1 row
[width]: VLOOKUP($B$5,$D$4:$G$10,3,FALSE) - This gives you the Col_Width value from column F for the corresponding Drop_Down_1 value, which is just the formula COUNTA to find out how many text values we have for the given range

 

[gecs]

The "complicated" part of the formula is replacing the additional columns you came up with.

Actually this part of the formula:
COUNTA(INDIRECT("$F$"&(MATCH($B$4,$E$4:$E$13,0)+3)&":$K$"&(MATCH($B$4,$E$4:$E$13,0)+3)))
replaces your second additional column and if you change the "K" in the above part with, let's say "Z", you can have up to 21 items in the second level list without the need of an additional column telling you how many items should be on that row - it is called a "dynamic range". The reason I did it like that (up to column K only) was that I didn't know if there is any useful data for further processing in that sheet beyond that column.

Also there is no need for the "Row Offset" column as long as you can calculate the row number for the offset function simply from the choice made on the first list - INDIRECT("$F$"&(MATCH($B$4,E4:E13,0)+3)).

If you replace the above sequences in your formula and delete the additional columns you end up with the same formula I posted before.

 

[abbymeredith]

have a look at this

http://www.contextures.com/xldataval02.html

Hi,

I followed the steps in this article and attempted to create a 3rd dependent list by creating named ranges based on the combination of the first two drop down boxes but it's not working. Any suggestions or ideas on what I might be doing wrong?

Thanks,

Abby

 

[Purplepixie]

I believe I am having a similar problem with my nested IF. I don't know if I am just not seeing the issue or pregnancy brain. Here is my issue:

I have a drop down with 2 options in E6 (Annual, Triennial). The date of the course taken in H6, Annual Days (365) in M2, Triennial Days (1095) in M3, Todays date in O2.


I would like I6 to look at E6 for "Annual" " and do =IF(($O$2-$H5)<=$M$2,"YES","NO") or "Triennial" and do =IF(($O$2-$H6)<=$M$3,"YES","NO"). Individually this works fine.


I tried half of it as :an AND/IF
=IF($E6="Triennial",AND(IF($O$2-$H6<=$M$3,"YES","NO")))

I don't think it understands $E6="Triennial"

What am I missing Ladies and Gentlemen?

 

[Purplepixie]

NVM, it was the pregnancy brain.

=IF($E6="Annual",IF($O$2-$H6<=$M$2,"YES","NO"),IF($E6="Triennial",IF($O$2-$H6<=$M$3,"YES","NO")))