Removing blank cells from a dependent drop list in a single column

Hi There

I need help with removing blank cells from a column which is a named range which in turn makes up a dependent droplist

Column A is a list of postcodes and column B is a list of building names which, as you may know, some buildings have the same postcode if they are on the same plot of land

I currently have a user selection screen which looks at

Area: (Data validation list called =Area)
Postcode: (Data validation list using =INDIRECT(Area cell reference)
Building Name: (Data validation list using =INDIRECT(Postcode cell reference)

The user selects and area which then shows the list of postcodes which, upon selection, shows the building names of the same post code

Currently I have 1968 buildings and around 1000 of those postcodes are unneeded duplicates

I can't post the exact data as it's sensitive information but I will try to put together a similar version if needed

I've looked at various OFFSET solutions but can't seem to get any of them to work/suit my format

Please help

Thanks

Dan

This could help

Array Formula condenses list then you set your dynamic named range to the new list. This removes duplicates and (all blanks but one). Modify to removed 2nd column IF="yes".

http://www.excelforum.com/excel-form...2-columns.html

Can also do it this way:

http://www.get-digital-help.com/2007...e-blank-cells/

Thanks everyone,

I've used the solution provided by Ursul as it appeared to work best with what data I had

Thanks again