Drop Down list Condition

[Revathi kannan]

Hi

Can someone help me out on Drop down list restriction.. I have listed out my requirement also provided snapshot. Please assist me. Thanks.

1. I have Country Airports in column D and their respective cities in Column E and my cities drop down is located in column K.

2. If i update china in column J then Column K should reflects only the China Cities.

3. Also if there are no multiple cities for the given country then it should automatically takes the single value in column K without any drop down.




Attachment 684218

[Limor_OP]

Why snapshot and not the file itself?

[Revathi kannan]

Sorry, i am not able to attach files since that option doesn't works for me..

[Limor_OP]

Maybe you are doing something wrong.
Look at the instructions at the top of the page, in the yellow banner.

[Revathi kannan]

Attached my file..Thanks for your assistance.

[alansidman]

Look at this link

https://www.thesmallman.com/cascading-drop-down-lists

[Revathi kannan]

Hi Alan,

Thanks for your time but i am not familiar with VBA. I thought of to fix my above requirements using excel functions.

[alansidman]

Here is a non VBA solution. If you google "Cascading Combo Boxes Excel" you will find other alternative solutions out there.

https://mauriceausum.com/2011/07/12/...oxes-in-excel/

[Limor_OP]

Hi,
you should use a helper, in Column G (drag down)

=IFERROR(INDEX($E$1:$E$9,AGGREGATE(15,6,(ROW($E$2:$E$9))/($J$2=$D$2:$D$9),ROWS($G$1:G1))),"")

In Data validation use:
=OFFSET($G$2,,,MATCH("",$G$2:$G$9,)-1)

[Dolphin Revanth]

Hi,

1. Arrange the Airport column to keep the same countries one below another. (Ref. attachment)

2. Use the following formula in data validation.
=OFFSET($C$3,MATCH($E6,$B$3:$B$10,0)-1,0,COUNTIF($B$3:$B$10,$E6))

3.You get the result

[Revathi kannan]

Hi Belinda,

Thanks for your help. It works perfectly but my 3rd point is not working.

For example: If i select Airport with one cities, it should not come under drop down the Column K should auto fill it since it has only one cities.
Example: If i select India in Column J then Column K should auto fill as "Chennai" wherein i do want to use drop for the single city.

[Revathi kannan]

Hi Dolphin,

Thanks for your help.. but my 3rd requirement is not working wherein as per your attachment if i type "India" in Column E it should auto fill the cell as "Chennai" in Column F since it has only one city so i don't need use drop down there. Is it possible.

[Dolphin Revanth]

Hi Revathi,

I don't think it is possible. But I will try to find a solution for that.

[Revathi kannan]

Thank you for your time Dolphin.

[Limor_OP]

Hi
Dont think also it is possible , maybe in vba which I am not prificient in.

[AliGW]

There is a way, however once a selection has been made, it will not work again.

In C3 copied down:

=IFNA(IF(COUNTIF($B$3:$B$10,$E3)=1,INDEX($C$3:$C$10,MATCH(E3,$B$3:$B$10,0)),""),"")