Data Validation With Multiple Criteria & Level

muhammad susanto · Dec 8, 2017

hi all...

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

City	Model	Type
Inner City	I	Arteri
Inner City	II	Arteri
Inner City	IIIA	Arteri
Inner City	IIIA	Kolektor
Inner City	IIIB	Kolektor
Inner City	IIIC	Jalan Lokal
Outer City	I	Arteri Primer
Outer City	I	Kolektor Primer
Outer City	I	Arteri Sekunder
Outer City	II	Kolektor Primer
Outer City	II	Arteri Sekunder
Outer City	II	Kolektor Sekunder
Outer City	IIIA	Kolektor Sekunder
Outer City	IIIA	Jalan Lokal
Outer City	IIIB1	Jalan Lokal/Parkir
Outer City	IIIB2	Jalan Lokal/Parkir
Outer City	IIIC	Jalan Lokal/Parkir

expected result :

Data Validation
City	Model	Type
Inner City	IIIA	Arteri
		Kolektor
Ineer city	I	Arteri

Ineer city	IIIB	Kolektor

- 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

muhammad susanto · Dec 9, 2017

hi Eric...you have help me like in M1 now i want too..you can make me drop down box in L1 based on K1 (Outer..& Inner...) like in M1

Eric W · Dec 9, 2017

See post 2 - it gives you the formula for the Data Validation rule in L1.

muhammad susanto · Dec 11, 2017

Code:

[COLOR=#333333]=OFFSET($E$2,0,MATCH(K1,$F$1:$I$1,0),MAX(IF(OFFSET($[/COLOR][COLOR=#ff0000]E$[/COLOR][COLOR=#333333]1:[/COLOR][COLOR=#ff0000]$E$1[/COLOR][COLOR=#333333]00,0,MATCH(K1,$F$1:$I$1,0))<>"",ROW($[/COLOR][COLOR=#ff0000]E$2[/COLOR][COLOR=#333333]:$[/COLOR][COLOR=#ff0000]E$[/COLOR][COLOR=#333333]100)))-1)[/COLOR]

hello Eric...

why not working?...i mean in L1

muhammad susanto · Dec 11, 2017

Excel 2007 32 bit#FFFFFF ;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6">

	A	B	C	E	F	G	K	L	M
1	City	model	tipe	City	Inner City	Outer City	Inner City	I,II,IIIA,IIIB,IIIC	Jalan Lokal/Parkir
2	Inner City	I	Arteri	Inner City	I	I
3	Inner City	II	Arteri	Outer City	II	II
4	Inner City	IIIA	Arteri		IIIA	IIIA
5	Inner City	IIIA	Kolektor		IIIB	IIIB1
6	Inner City	IIIB	Kolektor		IIIC	IIIB2
7	Inner City	IIIC	Jalan Lokal			IIIC
8	Outer City	I	Arteri Primer
9	Outer City	I	Kolektor Primer
10	Outer City	I	Arteri Sekunder
11	Outer City	II	Kolektor Primer
12	Outer City	II	Arteri Sekunder
13	Outer City	II	Kolektor Sekunder
14	Outer City	IIIA	Kolektor Sekunder
15	Outer City	IIIA	Jalan Lokal
16	Outer City	IIIB1	Jalan Lokal/Parkir
17	Outer City	IIIB2	Jalan Lokal/Parkir
18	Outer City	IIIC	Jalan Lokal/Parkir

rumus

#FFFFFF " >

Array Formulas#FFFFFF ;border-collapse: collapse; border-color: #A6AAB6">

Cell	Formula
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))),"")}
D3	{=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:$D3))),"")}
D4	{=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:$D4))),"")}
D5	{=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:$D5))),"")}
D6	{=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:$D6))),"")}
D7	{=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:$D7))),"")}
D8	{=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:$D8))),"")}
D9	{=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:$D9))),"")}
D10	{=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:$D10))),"")}
D11	{=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:$D11))),"")}
D12	{=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:$D12))),"")}
D13	{=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:$D13))),"")}
D14	{=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:$D14))),"")}
D15	{=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:$D15))),"")}
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)),"")}
G1	{=IFERROR(INDEX($A:$A,SMALL(IF($A$2:$A$100<>"",IF(COUNTIF($E$1:F1,$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)),"")}
G2	{=IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$100=G$1,IF(COUNTIF(G$1:G1,$B$2:$B$100)=0,ROW($A$2:$A$100))),1)),"")}
F3	{=IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$100=F$1,IF(COUNTIF(F$1:F2,$B$2:$B$100)=0,ROW($A$2:$A$100))),1)),"")}
G3	{=IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$100=G$1,IF(COUNTIF(G$1:G2,$B$2:$B$100)=0,ROW($A$2:$A$100))),1)),"")}
F4	{=IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$100=F$1,IF(COUNTIF(F$1:F3,$B$2:$B$100)=0,ROW($A$2:$A$100))),1)),"")}
G4	{=IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$100=G$1,IF(COUNTIF(G$1:G3,$B$2:$B$100)=0,ROW($A$2:$A$100))),1)),"")}
F5	{=IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$100=F$1,IF(COUNTIF(F$1:F4,$B$2:$B$100)=0,ROW($A$2:$A$100))),1)),"")}
G5	{=IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$100=G$1,IF(COUNTIF(G$1:G4,$B$2:$B$100)=0,ROW($A$2:$A$100))),1)),"")}
F6	{=IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$100=F$1,IF(COUNTIF(F$1:F5,$B$2:$B$100)=0,ROW($A$2:$A$100))),1)),"")}
G6	{=IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$100=G$1,IF(COUNTIF(G$1:G5,$B$2:$B$100)=0,ROW($A$2:$A$100))),1)),"")}
F7	{=IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$100=F$1,IF(COUNTIF(F$1:F6,$B$2:$B$100)=0,ROW($A$2:$A$100))),1)),"")}
G7	{=IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$100=G$1,IF(COUNTIF(G$1:G6,$B$2:$B$100)=0,ROW($A$2:$A$100))),1)),"")}
F8	{=IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$100=F$1,IF(COUNTIF(F$1:F7,$B$2:$B$100)=0,ROW($A$2:$A$100))),1)),"")}
G8	{=IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$100=G$1,IF(COUNTIF(G$1:G7,$B$2:$B$100)=0,ROW($A$2:$A$100))),1)),"")}
F9	{=IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$100=F$1,IF(COUNTIF(F$1:F8,$B$2:$B$100)=0,ROW($A$2:$A$100))),1)),"")}
G9	{=IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$100=G$1,IF(COUNTIF(G$1:G8,$B$2:$B$100)=0,ROW($A$2:$A$100))),1)),"")}
F10	{=IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$100=F$1,IF(COUNTIF(F$1:F9,$B$2:$B$100)=0,ROW($A$2:$A$100))),1)),"")}
G10	{=IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$100=G$1,IF(COUNTIF(G$1:G9,$B$2:$B$100)=0,ROW($A$2:$A$100))),1)),"")}
F11	{=IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$100=F$1,IF(COUNTIF(F$1:F10,$B$2:$B$100)=0,ROW($A$2:$A$100))),1)),"")}
G11	{=IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$100=G$1,IF(COUNTIF(G$1:G10,$B$2:$B$100)=0,ROW($A$2:$A$100))),1)),"")}
F12	{=IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$100=F$1,IF(COUNTIF(F$1:F11,$B$2:$B$100)=0,ROW($A$2:$A$100))),1)),"")}
F13	{=IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$100=F$1,IF(COUNTIF(F$1:F12,$B$2:$B$100)=0,ROW($A$2:$A$100))),1)),"")}
F14	{=IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$100=F$1,IF(COUNTIF(F$1:F13,$B$2:$B$100)=0,ROW($A$2:$A$100))),1)),"")}
F15	{=IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$100=F$1,IF(COUNTIF(F$1:F14,$B$2:$B$100)=0,ROW($A$2:$A$100))),1)),"")}
F16	{=IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$100=F$1,IF(COUNTIF(F$1:F15,$B$2:$B$100)=0,ROW($A$2:$A$100))),1)),"")}
F17	{=IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$100=F$1,IF(COUNTIF(F$1:F16,$B$2:$B$100)=0,ROW($A$2:$A$100))),1)),"")}
F18	{=IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$100=F$1,IF(COUNTIF(F$1:F17,$B$2:$B$100)=0,ROW($A$2:$A$100))),1)),"")}

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

i mean..in L1 is my problem..how to make Data Validation-List (drop down boxes)

muhammad susanto · Dec 11, 2017

i hope this link make more clear...
https://app.box.com/s/d28qkvvd0et9i33zav5xrnhuwpfxcu2l

my problem in cell L2 down...why?

Eric W · Dec 11, 2017

I'm not sure what you're asking. In your picture, the L1 cell shows all the options, which would not happen if you used the DV. And based on your picture, and last post, you're expecting something in L2? Are you putting the DV rule in multiple rows?

muhammad susanto · Dec 11, 2017

hello Eric....yes you're right...i think it need the DV rule in multiple rows..

bosco_yip · Dec 12, 2017

muhammad susanto said:
i hope this link make more clear...
https://app.box.com/s/d28qkvvd0et9i33zav5xrnhuwpfxcu2l

my problem in cell L2 down...why?

Try,

In L1 >> Data Validation

Allow : list

Source :

=OFFSET($C$1,MATCH(1,(J1=A$2:A$100)*(K1=B$2:B$100),0),,COUNTIFS(A$2:A$100,J1,B$2:B$100,K1))

and, copied down :

Regards
Bosco

muhammad susanto · Dec 12, 2017

helllo Bosco....wonderful!!!!!

WORKED GREAT!!! THANK YOU SO MUCH...

Data Validation With Multiple Criteria & Level

muhammad susanto

Well-known Member

muhammad susanto

Well-known Member

Excel Facts

Eric W

MrExcel MVP

muhammad susanto

Well-known Member

muhammad susanto

Well-known Member

muhammad susanto

Well-known Member

Eric W

MrExcel MVP

muhammad susanto

Well-known Member

bosco_yip

Well-known Member

muhammad susanto

Well-known Member

Similar threads

Forum statistics

Share this page

Data Validation With Multiple Criteria & Level

Well-known Member

Well-known Member

Excel Facts

MrExcel MVP

Well-known Member

Well-known Member

Well-known Member

MrExcel MVP

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock