How to lookup a different number of cities?

I have an activex combobox and 3 lists of cities in countries, when I select a country in the dropdown I want to lookup all cities in that country.

The number of cities varies between the countries.

E.g. Sweden might have 25 cities and Norway might have 12 cities.

See attached file.

Hi bakerman2,

thank you for your reply!

Your code works great and I get that I can change cell H6 and also expand it to more countries.


I have another question. If I have two comboboxes that are dependent on each other, like country and city.

How can I use SUMIFS and COUNTIFS on the first combobox1 and perform one lookup and then if I click the other combobox2 I want to perform a different lookup?

Ex. First I select Sweden and then I want to use one SUMIFS/COUNTIFS/VLOOKUP and then I want more granularity and I select Stockholm I want to use other SUMIFS/COUNTIFS/VLOOKUP?


Nested IF statements perhaps?

bakerman2,

would it be possible to resize sn to two or three columns?

Say there are 20 cities, then it would be in column (H6).

Say there are 40 cities, then it would be in (H6 and I6)

Say there are 60 cities, then it would be in (H6, I6 and J6)??

I think that I should access items 0-20, 21-40 and 41-60 using if statements or select case statements?

sn is an array?

To split the output, how about

Please Login or Register  to view this content.

Hi Fluff13,

thank you for your reply!

Your code is working really well. Thank you!

Glad to help & thanks for the feedback

I have an additional question, would it be possible to create hyperlinks in the same code?

So that each item becomes a hyperlink that links to it name??

Please Login or Register  to view this content.

Say Row1 and Row2 are Stockholm and Oslo.

Then when I change ComboBox1,

I get the named ranges with hyperlinks to sheets "Stockholm" and "Oslo"?