Data Validation With Multiple Criteria & Level

muhammad susanto

muhammad susanto

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

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

[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]City[/TH]
[TH]Model[/TH]
[TH]Type[/TH]
[/TR]
[TR]
[TD]Inner City[/TD]
[TD]I[/TD]
[TD]Arteri[/TD]
[/TR]
[TR]
[TD]Inner City[/TD]
[TD]II[/TD]
[TD]Arteri[/TD]
[/TR]
[TR]
[TD]Inner City[/TD]
[TD]IIIA[/TD]
[TD]Arteri[/TD]
[/TR]
[TR]
[TD]Inner City[/TD]
[TD]IIIA[/TD]
[TD]Kolektor[/TD]
[/TR]
[TR]
[TD]Inner City[/TD]
[TD]IIIB[/TD]
[TD]Kolektor[/TD]
[/TR]
[TR]
[TD]Inner City[/TD]
[TD]IIIC[/TD]
[TD]Jalan Lokal[/TD]
[/TR]
[TR]
[TD]Outer City[/TD]
[TD]I[/TD]
[TD]Arteri Primer[/TD]
[/TR]
[TR]
[TD]Outer City[/TD]
[TD]I[/TD]
[TD]Kolektor Primer[/TD]
[/TR]
[TR]
[TD]Outer City[/TD]
[TD]I[/TD]
[TD]Arteri Sekunder[/TD]
[/TR]
[TR]
[TD]Outer City[/TD]
[TD]II[/TD]
[TD]Kolektor Primer[/TD]
[/TR]
[TR]
[TD]Outer City[/TD]
[TD]II[/TD]
[TD]Arteri Sekunder[/TD]
[/TR]
[TR]
[TD]Outer City[/TD]
[TD]II[/TD]
[TD]Kolektor Sekunder[/TD]
[/TR]
[TR]
[TD]Outer City[/TD]
[TD]IIIA[/TD]
[TD]Kolektor Sekunder[/TD]
[/TR]
[TR]
[TD]Outer City[/TD]
[TD]IIIA[/TD]
[TD]Jalan Lokal[/TD]
[/TR]
[TR]
[TD]Outer City[/TD]
[TD]IIIB1[/TD]
[TD]Jalan Lokal/Parkir[/TD]
[/TR]
[TR]
[TD]Outer City[/TD]
[TD]IIIB2[/TD]
[TD]Jalan Lokal/Parkir[/TD]
[/TR]
[TR]
[TD]Outer City[/TD]
[TD]IIIC[/TD]
[TD]Jalan Lokal/Parkir[/TD]
[/TR]
</tbody>[/TABLE]

expected result :

[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]Data Validation[/TH]
[TH][/TH]
[TH][/TH]
[/TR]
[TR]
[TD]City[/TD]
[TD]Model[/TD]
[TD]Type[/TD]
[/TR]
[TR]
[TD]Inner City[/TD]
[TD]IIIA[/TD]
[TD]Arteri[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Kolektor[/TD]
[/TR]
[TR]
[TD]Ineer city[/TD]
[TD]I[/TD]
[TD]Arteri[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ineer city[/TD]
[TD]IIIB[/TD]
[TD]Kolektor[/TD]
[/TR]
</tbody>[/TABLE]

- 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:
Sort by date Sort by votes
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
 
Upvote 0

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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
 
Last edited:
Upvote 0
Excel 2007 32 bit#FFFFFF ;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6">
ABCDEFGHIJKLM
CitytipeCityInner CityOuter CityInner CityJalan Lokal/Parkir
Inner CityArteriInner CityII
Inner CityArteriOuter CityIIII
Inner CityArteriIIIAIIIA
Inner CityKolektorIIIBIIIB1
Inner CityKolektorIIICIIIB2
Inner CityJalan LokalIIIC
Outer CityArteri Primer
Outer CityKolektor Primer
Outer CityArteri Sekunder
Outer CityKolektor Primer
Outer CityArteri Sekunder
Outer CityKolektor Sekunder
Outer CityKolektor Sekunder
Outer CityJalan Lokal
Outer CityJalan Lokal/Parkir
Outer CityJalan Lokal/Parkir
Outer CityJalan Lokal/Parkir

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]model[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #FFFF00"]I,II,IIIA,IIIB,IIIC[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]I[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]II[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]IIIA[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]IIIA[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]IIIB[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]IIIC[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]I[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]I[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]I[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]II[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]II[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]II[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: center"]IIIA[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: center"]IIIA[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: center"]IIIB1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: center"]IIIB2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: center"]IIIC[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
rumus​

#FFFFFF " >[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas#FFFFFF ;border-collapse: collapse; border-color: #A6AAB6">[TABLE="width: 100%"]
<thead>[TR="bgcolor: #E0E0F0"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]D2[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]D3[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]D4[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]D5[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]D6[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]D7[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]D8[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]D9[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]D10[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]D11[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]D12[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]D13[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]D14[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]D15[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]F1[/TH]
[TD="align: left"]{=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)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]G1[/TH]
[TD="align: left"]{=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)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]F2[/TH]
[TD="align: left"]{=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)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]G2[/TH]
[TD="align: left"]{=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)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]F3[/TH]
[TD="align: left"]{=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)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]G3[/TH]
[TD="align: left"]{=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)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]F4[/TH]
[TD="align: left"]{=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)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]G4[/TH]
[TD="align: left"]{=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)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]F5[/TH]
[TD="align: left"]{=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)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]G5[/TH]
[TD="align: left"]{=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)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]F6[/TH]
[TD="align: left"]{=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)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]G6[/TH]
[TD="align: left"]{=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)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]F7[/TH]
[TD="align: left"]{=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)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]G7[/TH]
[TD="align: left"]{=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)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]F8[/TH]
[TD="align: left"]{=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)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]G8[/TH]
[TD="align: left"]{=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)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]F9[/TH]
[TD="align: left"]{=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)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]G9[/TH]
[TD="align: left"]{=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)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]F10[/TH]
[TD="align: left"]{=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)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]G10[/TH]
[TD="align: left"]{=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)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]F11[/TH]
[TD="align: left"]{=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)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]G11[/TH]
[TD="align: left"]{=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)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]F12[/TH]
[TD="align: left"]{=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)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]F13[/TH]
[TD="align: left"]{=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)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]F14[/TH]
[TD="align: left"]{=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)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]F15[/TH]
[TD="align: left"]{=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)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]F16[/TH]
[TD="align: left"]{=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)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]F17[/TH]
[TD="align: left"]{=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)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]F18[/TH]
[TD="align: left"]{=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)),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

i mean..in L1 is my problem..how to make Data Validation-List (drop down boxes)
 
Last edited:
Upvote 0
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?
 
Upvote 0
Upvote 0
You must log in or register to reply here.

Similar threads

muhammad susanto
  • Question Question
Data Validation With Unique Drop Down List
Replies
7
Views
755
muhammad susanto
muhammad susanto

Forum statistics

Threads
1,223,911
Messages
6,175,326
Members
452,635
Latest member
laura12345

Share this page

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