Cascading Data Validation List based on Multiple Conditions

bbalch

Board Regular
Joined
Feb 23, 2015
Messages
57
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?


AccountSubSplit
50165000-240Nash Tech
50165000-240iMis
50165055-240CS Dues
50165077-240AS Dues
50165077-240AS Misc Dues
50165000-120ASAE
50165000-120SHRM
50165000-130WSJ
50165000-140Billboard
50945000-120Cell Phone
50945000-120Conference Call
50945000-130Conference Call
50945000-160Bren
50945000-160Conference Call
50945000-160Sound Connect
50945000-160Go To Meeting
50945000-240Sound Connect
50945000-240JR / Ken

<tbody>
</tbody>


Thanks in advance for your help.
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

FlameRetired

Active Member
Joined
Feb 19, 2016
Messages
326
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
Code:
=$E$2:$E$3

For B1 follow the same instructions, but in Source enter this formula
Code:
=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
Code:
=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))



A
B
C
D
E
F
G
H
I
J
1
509450​
00-160​
Conference Call​
Account​
Account​
Sub​
Account​
Sub​
Split​
2
501650​
501650​
00-240​
501650​
00-240​
Nash Tech​
3
509450​
501650​
55-240​
501650​
00-240​
iMis​
4
501650​
77-240​
501650​
55-240​
CS Dues​
5
501650​
00-120​
501650​
77-240​
AS Dues​
6
501650​
00-130​
501650​
77-240​
AS Misc Dues​
7
501650​
00-140​
501650​
00-120​
ASAE​
8
509450​
00-120​
501650​
00-120​
SHRM​
9
509450​
00-130​
501650​
00-130​
WSJ​
10
509450​
00-160​
501650​
00-140​
Billboard​
11
509450​
00-240​
509450​
00-120​
Cell Phone​
12
509450​
00-120​
Conference Call​
13
509450​
00-130​
Conference Call​
14
509450​
00-160​
Bren​
15
509450​
00-160​
Conference Call​
16
509450​
00-160​
Sound Connect​
17
509450​
00-160​
Go To Meeting​
18
509450​
00-240​
Sound Connect​
19
509450​
00-240​
JR / Ken​
 
Last edited:

bbalch

Board Regular
Joined
Feb 23, 2015
Messages
57
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 >:
Code:
=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

Active Member
Joined
Feb 19, 2016
Messages
326
...... 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 -?
 
Last edited:

bbalch

Board Regular
Joined
Feb 23, 2015
Messages
57

ADVERTISEMENT

search
I think its an issue with the formula.
search
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

Active Member
Joined
Feb 19, 2016
Messages
326
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

Board Regular
Joined
Feb 23, 2015
Messages
57
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

Active Member
Joined
Feb 19, 2016
Messages
326
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,736
Messages
5,597,815
Members
414,178
Latest member
Octavian Manoli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top