Dear all
I am trying to create a sheet that contains a drop-down list of countries (B2) and in F2 I would like to have a drop-down list with the cities of the country I select in B2. See attached sample for clarification. Does anyone have a solution to this? I can't use Data Validation because I have too many countries. Any help would be highly appreciated.
Thank you in advance for your time.
Re: Display selection based on a value in another cell
Here is a VBA solution-->http://www.excel-easy.com/vba/…ependent-combo-boxes.html
and a DV solution-->https://mauriceausum.com/2011/…ing-combo-boxes-in-excel/
Re: Display selection based on a value in another cell
Hi,
I am not sure what alternative to Data Validation you would want.....
... But, if you put this formula in the Source field for Data Validation >> List in F2 then it should work to show only the cities for the country you pick in B2.
[COLOR="#0000FF"]=OFFSET($O$2,MATCH($B$2,$N$2:$N$15,0)-1,,COUNTIF($N$2:$N$15,$B$2),1)[/COLOR]
Re: Display selection based on a value in another cell
Dear NBVC
thank you very much for the solution, it works perfectly.
Dear AlanSidman
thank you also for your advise, I was trying to avoid VBA as it is too complicated to explain to people who are not even familiar with Excel. I truly appreciate your time to answer my question though.
Kind regards
Re: Display selection based on a value in another cell
A more flexible approach using Excel Tables - see sheet2.
Excel Table created to maintain list of countries and cities.
Named Ranges are based on the Table
Country: =INDEX(Table1[COUNTRY],1):INDEX(Table1[COUNTRY],COUNTA(Table1[COUNTRY]))
City_List: =INDEX(Table1,1,MATCH(Sheet2!G11,Table1[#Headers],0)):INDEX(Table1,COUNTA(INDEX(Table1,,MATCH(Sheet2!G11,Table1[#Headers],0))),MATCH(Sheet2!G11,Table1[#Headers],0))
Re: Display selection based on a value in another cell
Dear AAE
thank you, a nice solution indeed. I am just a little worried that the people who will have to use it won't understand this formula. I will try it out for myself though. Again, thanks for your suggestion, I truly appreciate your time.
Don’t have an account yet? Register yourself now and be a part of our community!
