data validation list - Select all criteria.

[Kumar Shanmugam]

Hi,

I have a sample data if you select zone - North/South/West/east you can select the respective state in that zone.

I have used data validation list and create from selection to create a dependent drop down list. Data validation has limitation that if I wanted to select multiple state in the dependent drop down list - for eg If I wanted to selection Tamil Nadu and Kerala in South Zone

IF it is possible to do , what would be the effect on luke's formula Will there be any effect?

=IF(COUNTIFS('2W-NDCC'!$A$1:$A$5000,'Dashboard NDCC'!$B$1,'2W-NDCC'!$B$1:$B$5000,'Dashboard NDCC'!$D$1,'2W-NDCC'!$J$1:$J$5000,">="&'Dashboard NDCC'!$J$1,'2W-NDCC'!$H$1:$H$5000,">="&$L$1)<ROWS($A$5:A5),"",INDEX('2W-NDCC'!$C$1:$C$5000,SMALL(IF(('2W-NDCC'!$A$1:$A$5000='Dashboard NDCC'!$B$1)+('2W-NDCC'!$B$1:$B$5000='Dashboard NDCC'!$D$1)+('2W-NDCC'!$J$1:$J$5000>='Dashboard NDCC'!$J$1)+('2W-NDCC'!$H$1:$H$5000>=$L$1)=4,ROW('2W-NDCC'!$C$1:$C$5000)),ROW(C1))))

 

[AVK]

One way you can do.
In sheet "2W-NDCC" column "B" Formula :
=IF('Dashboard NDCC'!$B$1='2W-NDCC'!$A4,'Dashboard NDCC'!$D$1&" "&'Dashboard NDCC'!$D$2,"")
Copy down.

In sheet "Dashboard NDCC" in "D2" add datavalidation
formula in "A5"
=IF(COUNTIFS('2W-NDCC'!$A$1:$A$5000,'Dashboard NDCC'!$B$1,'2W-NDCC'!$B$1:$B$5000,'Dashboard NDCC'!$D$1&" "&$D$2,'2W-NDCC'!$J$1:$J$5000,">="&'Dashboard NDCC'!$J$1,'2W-NDCC'!$H$1:$H$5000,">="&$L$1)<ROWS($A$5:A5),"",INDEX('2W-NDCC'!$C$1:$C$5000,SMALL(IF(('2W-NDCC'!$A$1:$A$5000='Dashboard NDCC'!$B$1)+('2W-NDCC'!$B$1:$B$5000='Dashboard NDCC'!$D$1&" "&$D$2)+('2W-NDCC'!$J$1:$J$5000>='Dashboard NDCC'!$J$1)+('2W-NDCC'!$H$1:$H$5000>=$L$1)=4,ROW('2W-NDCC'!$C$1:$C$5000)),ROW(C1))))
change formula from column "B" to "M" for more details look attach file.