How to remove blank values from drop down list

**funnysachin** · 07-14-2011, 02:09 PM

All,
Ok, have come across many solutions for removing blank values from a drop down list but it seems none of that is useful in my situation.

I have two tabs in the excel 2007 file - tab 1 and tab 2. I have following values on tab 1

Value1

Value2

Value3

There are blank cells in between and they are not consistent. I haved named this entire range as MyRange and using it on tab2 to populate a drop down list using =MyRange.

While showing MyRange values on tab 2 in the drop down list, I want to eliminate the blank values and show only Value1, Value2 and Value3.

Does anyone know how I can do it? Thanks in advance.

**NBVC** · 07-14-2011, 02:17 PM

Perhaps you have come across this solution, but it is the one I'd use.. and that is make a new list separately (you can do it with formulas if your list might be dynamic) and then call that list MyRange instead.

**funnysachin** · 07-14-2011, 02:20 PM

NBVC,
Thanks for a quick response? Is there no other way? like using OFFSET etc.?

**NBVC** · 07-14-2011, 02:28 PM

Not that I am aware of.

You need to have a final list with no blanks between.. if all the blanks are below, then it is possible, but if not, you need to create a separate list (manually or with dynamic formulas) and reference that.... or you can possibly use VBA... but not my expertise.....