Data Validation With Multiple Criteria & Level

muhammad susanto

Well-known Member
Joined
Jan 8, 2013
Messages
2,077
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
hi all...

i have problem how to make data validation list/formula with multiple criteria

CityModelType
Inner CityIArteri
Inner CityIIArteri
Inner CityIIIAArteri
Inner CityIIIAKolektor
Inner CityIIIBKolektor
Inner CityIIICJalan Lokal
Outer CityIArteri Primer
Outer CityIKolektor Primer
Outer CityIArteri Sekunder
Outer CityIIKolektor Primer
Outer CityIIArteri Sekunder
Outer CityIIKolektor Sekunder
Outer CityIIIAKolektor Sekunder
Outer CityIIIAJalan Lokal
Outer CityIIIB1Jalan Lokal/Parkir
Outer CityIIIB2Jalan Lokal/Parkir
Outer CityIIICJalan Lokal/Parkir

<tbody>
</tbody>

expected result :

Data Validation
CityModelType
Inner CityIIIAArteri
Kolektor
Ineer cityIArteri
Ineer cityIIIBKolektor

<tbody>
</tbody>

- when i select Inner City , in the cell adjacent possible in Model show I, II, IIIA, IIIB, IIIC...then i select I ....shown Arteri..

please, don't use vba/macro.
any help is greatly appreciated....
m.susanto
 
Last edited:

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
There are a few ways to do this, here's one:

ABCDEFGHIJKLM
1CityModelTypeCityInner CityOuter CityInner CityIIIBKolektor
2Inner CityIArteriII0
3Inner CityIIArteriIIII
4Inner CityIIIAArteriIIIAIIIA
5Inner CityIIIAKolektorIIIBIIIB1
6Inner CityIIIBKolektorIIICIIIB2
7Inner CityIIICJalan LokalIIIC
8Outer CityIArteri Primer
9Outer CityIKolektor Primer
10Outer CityIArteri Sekunder
11Outer CityIIKolektor Primer
12Outer CityIIArteri Sekunder
13Outer CityIIKolektor Sekunder
14Outer CityIIIAKolektor Sekunder
15Outer CityIIIAJalan Lokal
16Outer CityIIIB1Jalan Lokal/Parkir
17Outer CityIIIB2Jalan Lokal/Parkir
18Outer CityIIICJalan Lokal/Parkir
19
20

<tbody>
</tbody>
Sheet1

Array Formulas
CellFormula
F1{=IFERROR(INDEX($A:$A,SMALL(IF($A$2:$A$100<>"",IF(COUNTIF($E$1:E1,$A$2:$A$100)=0,ROW($A$2:$A$100))),1)),"")}
F2{=IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$100=F$1,IF(COUNTIF(F$1:F1,$B$2:$B$100)=0,ROW($A$2:$A$100))),1)),"")}
M1{=INDEX(C:C,MATCH(K1&"|"&L1,$A$1:$A$100&"|"&$B$1:$B$100,0))}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>






Your table is in A1:C100. If your actual table is longer, then change the references in the formulas. Put the F1 formula in, confirm with Control+Shift+Enter, then drag to the right as far as needed. This will create a unique list of cities. Now put the F2 formula in, confirm with CSE, and drag it down as needed. Then paste it to as many other columns are needed.

Now select K1, click Data Validation > select List > and enter this formula:

=OFFSET($F$1,0,0,1,MAX(IF($F$1:$J$1<>"",COLUMN($F$1:$J$1)-COLUMN($F$1)+1)))

Select L1, click Data Validation > select List > and enter this formula:

=OFFSET($E$2,0,MATCH(K1,$F$1:$I$1,0),MAX(IF(OFFSET($E$1:$E$100,0,MATCH(K1,$F$1:$I$1,0))<>"",ROW($E$2:$E$100)))-1)

Finally, enter the M1 formula, confirm with CSE.

Now you can add rows to your table (up to 100), and the other tables, and the Data Validation rules, will all adapt automatically. You'll probably want to put the tables on their own sheet, and the drop-down boxes elsewhere. Also, if there are duplicate keys in column A and B, the formula in M1 will find just the first. (Rows 8-10 for example.)

Let me know if you have any questions.
 
Upvote 0
for outer city is not fully working...

how to show/display :
Outer city..model 1, contains 3 possible Arteri Primer,Kolektor Primer, Arteri Sekunder
in M1, i want show 3 possible that {Arteri Primer,Kolektor Primer, Arteri Sekunder}
 
Upvote 0
Change the M1 formula to:

=IFERROR(INDEX(C:C,SMALL(IF($A$2:$A$18=K$1,IF($B$2:$B$18=L$1,ROW($A$2:$A$18))),ROWS($M$1:$M1))),"")
and confirm with Control+Shift+Enter.

Now drag it down the column as needed.
 
Upvote 0
hi Eric,,,thanks for your help....
after use your formula =IFERROR(INDEX(C:C,SMALL(IF($A$2:$A$18=K$1,IF($B$2:$B$18=L$1,ROW($A$2:$A$18))),ROWS($M$1:$M1))),"")

in M1, that showing 3 possible that {Arteri Primer,Kolektor Primer, Arteri Sekunder}...
how to fix it in List
drop-down boxes in Data Validation , i mean in M1....
 
Upvote 0
Oh, I see. We still need that formula, but let's move it to column D. Put Type in D1, and this formula in D2:

=IFERROR(INDEX(C:C,SMALL(IF($A$2:$A$100=K$1,IF($B$2:$B$100=L$1,ROW($A$2:$A$100))),ROWS($D$2:$D2))),"")
confirm with CSE and drag down.

Delete the formulas from column M. Now select M1, click Data Validation > choose List > and enter:

=OFFSET($D$2,0,0,MAX(IF($D$1:$D$100<>"",ROW($D$1:$D$100)-1)))

That should do it.
 
Upvote 0
hi Eric.....really great!! that's i want it...
You're kind man..nice chatting wit:)h you.....
 
Upvote 0
once again Eric...please help me ..
if in col. K contains Inner City,,i want in col. L drop-down boxes list contains I, II, IIIA, IIIB, IIIC, otherwise if in col. K contains Outer city in col. L drop-down boxes list contains I, II, IIIA, IIIB1, IIIB2, IIIC.

please..would you help me...?
 
Upvote 0
Not sure what your problem is? When I use the drop down lists as I explained them in my sample sheet, they give me the lists you say.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

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
Back
Top