Dynamic Drop Down List - exclude blanks

**Econocrat** · 08-24-2012, 11:08 AM

Hi. I am trying to create a drop down list that automatically excludes blank cells.

For Excel 2010, the following works no problem when included in the Define Name window:

=formula!$U$2:INDEX($U$2:$U$60,COUNTIF($U$2:$U$60,"?*")

However, in Excel 2007, the same formula does not work. For more information on how I am getting here: http://www.youtube.com/watch?v=6PcF04bTSOM

Go to the 18:30 mark.

Thank you,

**NBVC** · 08-24-2012, 11:12 AM

Do you need to include the sheetname, formula, in the INDEX and COUNTIF sections?

**Econocrat** · 08-24-2012, 11:20 AM

Correct, I am sorry. I did not copy it from the defined name window where those things were added.

=formula!$U$2:INDEX(Ref_Employee_SRC!$U$2:$U$60,COUNTIF(Ref_Employee_SRC!$U$2:$U$60,"?*"))

Note: I have looked into the offset function. I really don't want to create a new list somewhere else.

My source list is constantly changing and I have many dynamic drop down lists to create. Having one arrary to sort my data and then another array to delete the spaces would add a high level of complexity.

thank you,

**NBVC** · 08-24-2012, 11:22 AM

But now you are starting at sheet called formula U2 and ending in sheet Ref_Employee_SRC. That mix can't happen.

**Econocrat** · 08-24-2012, 11:36 AM

OK, I see your point. That whole formula is used in the Name Manager window. It is not used in the cell.

**NBVC** · 08-24-2012, 12:21 PM

if you put this in the name manager:

=Ref_Employee_SRC!$U$2:INDEX(Ref_Employee_SRC!$U$2:$U$60,COUNTIF(Ref_Employee_SRC!$U$2:$U$60,"?*"))

it should work, if U2:U60 contain text and formula nulls....

**Econocrat** · 08-24-2012, 12:51 PM

Book1.xlsx

I am trying to break down the problem. I still can't get things to work. Everything is fine until I enter a dynamic range into the name manager.

**NBVC** · 08-24-2012, 02:10 PM

You can't use INDIRECT references in Data Validation with Dynamic Named Range... so we will need to employ method described here at the bottom: Using Dynamic Lists

So, first change reference for the Employee_AC to just =Ref_Employee_SRC!$C$1

then create a named range called Employee_AC_Col and make it the whole column = =Ref_Employee_SRC!$C:$C

Then for data validation, use List and =OFFSET(INDIRECT($F7),1,0,COUNTIF(INDIRECT($F7&"_Col"),"?*")-1)