Combo box and 1700 blank entries

**philipbc** · 05-03-2010, 07:07 PM

Hi all, I have done my best to search this and other forums to find a solution to my problem as I thought it would be common however I am yet to find an applicable answer. I have 17500 rows each with a country and a city, I have two combo boxes the first selects the country from a condensed list of 95 countries. I then have an IF function in the column next to the city stating if the country in that row = country selected in 'combo box 1' display city, if FALSE display "".

Then I have a second combo box that has the entire IF city column range selected, of course it displays all 1750 values whether they are a city or just blank (""). It is a pain to have to search through the combo box to find the non blank values.

Sorry for the long winded explanation but essentially what I am trying to do is either:
1. Get combo box to ignore ""
OR
2. populate a new range with only the country selected's cities without all the blanks

Hope this makes sense my programming is very limited but excel skills are quite good,
thanks for any suggestions
-phil

**davesexcel** · 05-03-2010, 07:19 PM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**philipbc** · 05-03-2010, 07:28 PM

Hi, thanks for the suggestion, I don't really know how to put a dummy worksheet up so here is the link to the real one, there is no sensitive information. It should be fairly clear,
https://docs.google.com/leaf?id=0BwL...mZTU4&hl=en_GB
There is no need to enable macros to observe the problem
Thanks
-phil

**davesexcel** · 05-03-2010, 07:51 PM

Nobody really wants to download a 1.7mb workbook.
Resave your workbook as a different name.
If you have 1700 lines of data, make it just 100 lines of data.
If you have sheets that have nothing to do with your question, then delete them

Click the "New Post" Button, then click the Paper Clip Icon and upload your small example.