Ignore blank cells with formulas in Drop Down List

Question

This is where all my values are. Range O2 to O50 is populated by formulas. Some return a value while some do not return any values.

When I try to create a dropdown list, I use this formula in the Name Manager. =OFFSET(‘Sheet1’!$O$2,0,0,COUNTA(‘Sheet1’!$O:$O),1)

But this formula will include all the blank cells because they have formulas inside.

I cannot erase these formulas even they don’t return any values. Is there a way to ask Excel to ignore blank cells (even they have formulas inside of them) when creating a drop-down list?

Answer 1

I think you can get to this with COUNTBLANK (assuming there's a block of values at the top of $0$2:$0$50 without any blanks interspersed).

=OFFSET('Sheet1'!$O$2,0,0,COUNTA('Sheet1'!$O$2:$O$50)-COUNTBLANK('Sheet1'!$O$2:$O$50),1)

In action: