How to create a dropdown based on multiple inputs

[Santanu Chatterjee]

Dear All,

I need a help with a situation where I need to create a variable drop down based on input in other fields. I am trying to map the airlines that operate in the morning hours & afternoon hours based on an input of "Slot" (Morning/Afternoon),"City" (Kolkata/Delhi) and "Connection mode" (Commercial/BD/Surface).

**At present only airlines data has been made available under "commercial" while the BD & Surface has no data. A "-" symbol should show in the corresponding dropdown if those two are selected.

The data for the same in given in the attached file. The formula I had prepared to put in the data validation is :

IF(AND(N3="morning",O3="kolkata",P3="commercial"),OFFSET($G$2,1,MATCH($P$3,$G$2:$I$2,0)-1,COUNTA(OFFSET($G$2,1,MATCH($P3,$G$2:$I$2,0)-1,4,1)),1),IF(AND(N3="afternoon",O3="kolkata",P3="commercial"),OFFSET($J$2,1,MATCH($P3,$J$2:$L$2,0)-1,COUNTA(OFFSET($J$2,1,MATCH($P3,$J$2:$L$2,0)-1,4,1)),1)),IF(AND(N3="MORNING",O3="DELHI",P3="COMMERCIAL"),OFFSET($A$2,1,MATCH($P3,$A$2:$C$2,0)-1,COUNTA(OFFSET($A$2,1,MATCH($P3,$A$2:$C$2,0)-1,4,1)),1)),OFFSET($D$2,1,MATCH($P3,$D$2:$F$2,0)-1,COUNTA(OFFSET($D$2,1,MATCH($P3,$D$2:$F$2,0)-1,4,1)),1)))

But the above mentioned formula is not working as all the nested ifs are not functioning. Its giving an error "You have entered too many arguments for this function".

Could you please help me understand how should I go about it?

Regards,
Santanu

 

[bosco_yip]

Try'

1] In G3 >> Data >> Data validation >>

• Allow : List
• Source : =OFFSET($A$3,,MATCH($O$3&"-"&LEFT($N$3),$A$1:$L$1,0)+MATCH($P$3,$A$2:$C$2,0)-2,3)
• OK

2] Click N3, O3 and F3 dropdown list, and change information for checking purpose.

 

[Santanu Chatterjee]

Dear Bosco,

Thanks for the help. It worked.

Regards,
Santanu