Cascading Data Validation List based on Multiple Conditions

[bbalch]

I have a table with the following columns: Account, Sub, and Split. I'm trying to create cascading data validation lists where you first select the Account, then the Sub, and finally the Split. So in the table below if I chose Account = 501650, then Sub = 00-240, the third Split list should include Nash Tech and iMis. Or if I chose Account = 504950, then 00-120, the Split list should include Cell Phone and Conference Call.

All the articles and videos I've found used named ranges to populate a list based on 1 lookup value. This scenario is more like an index match match where there are two look up values.

Desired output:
The Account data validation list should include: 501650 and 509450. Assuming 501650 is selected
The Sub data validation should include: 00-240, 55-240, 77-240, 99-240, 00-120, 00-130, and 00-140
The Split list would be based on the value selected above.

Does anybody have any suggestions on how to create a data validation list for the Split column based on the Account selected then the Sub selected?


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Account[/TD]
[TD]Sub[/TD]
[TD]Split[/TD]
[/TR]
[TR]
[TD]501650[/TD]
[TD]00-240[/TD]
[TD]Nash Tech[/TD]
[/TR]
[TR]
[TD]501650[/TD]
[TD]00-240[/TD]
[TD]iMis[/TD]
[/TR]
[TR]
[TD]501650[/TD]
[TD]55-240[/TD]
[TD]CS Dues[/TD]
[/TR]
[TR]
[TD]501650[/TD]
[TD]77-240[/TD]
[TD]AS Dues[/TD]
[/TR]
[TR]
[TD]501650[/TD]
[TD]77-240[/TD]
[TD]AS Misc Dues[/TD]
[/TR]
[TR]
[TD]501650[/TD]
[TD]00-120[/TD]
[TD]ASAE[/TD]
[/TR]
[TR]
[TD]501650[/TD]
[TD]00-120[/TD]
[TD]SHRM[/TD]
[/TR]
[TR]
[TD]501650[/TD]
[TD]00-130[/TD]
[TD]WSJ[/TD]
[/TR]
[TR]
[TD]501650[/TD]
[TD]00-140[/TD]
[TD]Billboard[/TD]
[/TR]
[TR]
[TD]509450[/TD]
[TD]00-120[/TD]
[TD]Cell Phone[/TD]
[/TR]
[TR]
[TD]509450[/TD]
[TD]00-120[/TD]
[TD]Conference Call[/TD]
[/TR]
[TR]
[TD]509450[/TD]
[TD]00-130[/TD]
[TD]Conference Call[/TD]
[/TR]
[TR]
[TD]509450[/TD]
[TD]00-160[/TD]
[TD]Bren[/TD]
[/TR]
[TR]
[TD]509450[/TD]
[TD]00-160[/TD]
[TD]Conference Call[/TD]
[/TR]
[TR]
[TD]509450[/TD]
[TD]00-160[/TD]
[TD]Sound Connect[/TD]
[/TR]
[TR]
[TD]509450[/TD]
[TD]00-160[/TD]
[TD]Go To Meeting[/TD]
[/TR]
[TR]
[TD]509450[/TD]
[TD]00-240[/TD]
[TD]Sound Connect[/TD]
[/TR]
[TR]
[TD]509450[/TD]
[TD]00-240[/TD]
[TD]JR / Ken[/TD]
[/TR]
</tbody>[/TABLE]


Thanks in advance for your help.

 

[FlameRetired]

This proposal splits the validation lists in stair step fashion. With the lists in column E:J as below.

For column E: Copy and paste column H into E. Apply remove duplicates. You will have two items left. This will be the DV list for the first drop down.
For columns F:G: Copy and paste columns H:I int F:G. With both F:G still selected apply remove duplicates. You will have 10 pairs of items. This will be used for the second drop down.
For the last drop down leave H:J in place.

To define the drop down in A1 make A1 the active cell. Click Data >> Data validation. From the Settings tab select List from the Allow drop down. In Source put this range

=$E$2:$E$3

For B1 follow the same instructions, but in Source enter this formula

=OFFSET($G$2,MATCH($A$1,$F$2:$F$11,0)-1,,COUNTIF($F$2:$F$11,$A$1))

For C1 follow the same instructions, but in Source enter this formula

=OFFSET($J$2,MATCH($B$1,$I$2:$I$19,0)-1,,COUNTIFS($H$2:$H$19,$A$1,$I$2:$I$19,$B$1))

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][td="bgcolor: #DCE6F1"]

I​

[/td][td="bgcolor: #DCE6F1"]

J​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td="bgcolor:#FFFF00"]

509450​

[/td][td="bgcolor:#DDEBF7"]

00-160​

[/td][td="bgcolor:#F4B084"]

Conference Call​

[/td][td][/td][td]

Account​

[/td][td]

Account​

[/td][td]

Sub​

[/td][td]

Account​

[/td][td]

Sub​

[/td][td]

Split​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#FFFF00"]

501650​

[/td][td="bgcolor:#DDEBF7"]

501650​

[/td][td="bgcolor:#DDEBF7"]

00-240​

[/td][td="bgcolor:#F4B084"]

501650​

[/td][td="bgcolor:#F4B084"]

00-240​

[/td][td="bgcolor:#F4B084"]

Nash Tech​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#FFFF00"]

509450​

[/td][td="bgcolor:#DDEBF7"]

501650​

[/td][td="bgcolor:#DDEBF7"]

55-240​

[/td][td="bgcolor:#F4B084"]

501650​

[/td][td="bgcolor:#F4B084"]

00-240​

[/td][td="bgcolor:#F4B084"]

iMis​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#DDEBF7"]

501650​

[/td][td="bgcolor:#DDEBF7"]

77-240​

[/td][td="bgcolor:#F4B084"]

501650​

[/td][td="bgcolor:#F4B084"]

55-240​

[/td][td="bgcolor:#F4B084"]

CS Dues​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#DDEBF7"]

501650​

[/td][td="bgcolor:#DDEBF7"]

00-120​

[/td][td="bgcolor:#F4B084"]

501650​

[/td][td="bgcolor:#F4B084"]

77-240​

[/td][td="bgcolor:#F4B084"]

AS Dues​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#DDEBF7"]

501650​

[/td][td="bgcolor:#DDEBF7"]

00-130​

[/td][td="bgcolor:#F4B084"]

501650​

[/td][td="bgcolor:#F4B084"]

77-240​

[/td][td="bgcolor:#F4B084"]

AS Misc Dues​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#DDEBF7"]

501650​

[/td][td="bgcolor:#DDEBF7"]

00-140​

[/td][td="bgcolor:#F4B084"]

501650​

[/td][td="bgcolor:#F4B084"]

00-120​

[/td][td="bgcolor:#F4B084"]

ASAE​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#DDEBF7"]

509450​

[/td][td="bgcolor:#DDEBF7"]

00-120​

[/td][td="bgcolor:#F4B084"]

501650​

[/td][td="bgcolor:#F4B084"]

00-120​

[/td][td="bgcolor:#F4B084"]

SHRM​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#DDEBF7"]

509450​

[/td][td="bgcolor:#DDEBF7"]

00-130​

[/td][td="bgcolor:#F4B084"]

501650​

[/td][td="bgcolor:#F4B084"]

00-130​

[/td][td="bgcolor:#F4B084"]

WSJ​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#DDEBF7"]

509450​

[/td][td="bgcolor:#DDEBF7"]

00-160​

[/td][td="bgcolor:#F4B084"]

501650​

[/td][td="bgcolor:#F4B084"]

00-140​

[/td][td="bgcolor:#F4B084"]

Billboard​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#DDEBF7"]

509450​

[/td][td="bgcolor:#DDEBF7"]

00-240​

[/td][td="bgcolor:#F4B084"]

509450​

[/td][td="bgcolor:#F4B084"]

00-120​

[/td][td="bgcolor:#F4B084"]

Cell Phone​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#F4B084"]

509450​

[/td][td="bgcolor:#F4B084"]

00-120​

[/td][td="bgcolor:#F4B084"]

Conference Call​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#F4B084"]

509450​

[/td][td="bgcolor:#F4B084"]

00-130​

[/td][td="bgcolor:#F4B084"]

Conference Call​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

14​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#F4B084"]

509450​

[/td][td="bgcolor:#F4B084"]

00-160​

[/td][td="bgcolor:#F4B084"]

Bren​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

15​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#F4B084"]

509450​

[/td][td="bgcolor:#F4B084"]

00-160​

[/td][td="bgcolor:#F4B084"]

Conference Call​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

16​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#F4B084"]

509450​

[/td][td="bgcolor:#F4B084"]

00-160​

[/td][td="bgcolor:#F4B084"]

Sound Connect​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

17​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#F4B084"]

509450​

[/td][td="bgcolor:#F4B084"]

00-160​

[/td][td="bgcolor:#F4B084"]

Go To Meeting​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

18​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#F4B084"]

509450​

[/td][td="bgcolor:#F4B084"]

00-240​

[/td][td="bgcolor:#F4B084"]

Sound Connect​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

19​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#F4B084"]

509450​

[/td][td="bgcolor:#F4B084"]

00-240​

[/td][td="bgcolor:#F4B084"]

JR / Ken​

[/td][/tr]
[/table]

 

[bbalch]

This is great! Thank you very much!

I turn the following datasets into individual tables: E1:E3 = act_tbl, F1:G11 = sub_tbl, and H1:J19 = data_tbl.

I adjusted the formula from above for cell B1 to be (Data Validation > From List > Source >:

=OFFSET($G$2,MATCH($A$1,sub_tbl[Account],0)-1,,COUNTIF(sub_tbl[Account],$A$1))

The formula seems to works as the correct range cells in column G are selected with the "marching ants" going around them. However, there is an error message in the Data Validation screen that pops up: "There's a problem with this formula. Not Trying to type a formula? When the first character is an equal(=) or minus (-) sign, Excel thinks it's a formula...."

Do you know how to correct or get around this error message?

 

[FlameRetired]

...... The formula seems to works as the correct range cells in column G are selected with the "marching ants" going around them. However, there is an error message in the Data Validation screen that pops up: "There's a problem with this formula. Not Trying to type a formula? When the first character is an equal(=) or minus (-) sign, Excel thinks it's a formula...."

I've never seen this message from DV manager before, or are you saying some of the items in the DV list have leading = or -?

 

[bbalch]

I think its an issue with the formula.

I tried to post an image of the error message but I don't think it's going to work. https://support.office.com/en-us/ar...formulas-8309381d-33e8-42f6-b889-84ef6df1d586 I found this microsoft office article when searching for the error message.

 

[FlameRetired]

I've also never used table syntax in DV definitions. How does it work using plain vanilla cell ranges as I posted? Do you still get the same error message?

 

[bbalch]

The Data Validation works correctly when using the plain cell ranges. I was trying to use tables to accommodate growth in the lists, but that might not be possible.

 

[FlameRetired]

Try increasing the ranges in MATCH and COUNTIF(S) enough to "future proof" the ranges or keep those ranges up to date with dynamic named ranges. They also resize to fit the data.