Excel drop down list with Dynamic range where range contains formulas that return a ""

[Alwina]

Hi there

I have a drop down list which gets its data from the output of a formula in column I. The formula in column I is

=IFERROR(INDEX(Projects!$B$12:$B$500, SMALL(INDEX(($I$10=Projects!$A$12:$A$500)*(MATCH(ROW(Projects!$A$12:$A$500), ROW(Projects!$A$12:$AB$500)))+($I$10<>Projects!$A$12:$A$500)*1048577, 0, 0), ROW(A1))),"")

The formula in the data valadation list is =OFFSET($I$11,0,0,COUNTA($I:$I)-7,1)

What can i do to exclude the cells that returns ""?

 

[Shrikant]

Try this one:


In your offset function replace COUNTA($I:$I) with COUNTA($I:$I)-COUNTIF($I:$I,"")