Dynamic Input Range, Range = to Cells >""

Hello,

I have an Form Control drop down box, I want to add a dynamic range so it lists only a list of value > "" (greater than blank).

I've tried the various named range options i.e =OFFSET($A$1,0,0,COUNTA($A$1:$A$40),1) and a number of other methods using =EVALUATE(ie.NamedRange) but none of these work?!

My problem is, I have a range from 1-40 with formulas, where my formula result is "" (blank) I want my dynamic range to end so its could be A1:A12 where the formula returns "" in cell A13. The list is sorted using an array formula so that the first blank is in effect the last cell of the range - 1.

Does this make sense? How do I create the input range for this?

Am I able to attach an example?

I have used ">""", I tried this =OFFSET($A$1,0,0,COUNTIF($A$1:$A$40,">"""),1) with no success.

I am able to get the range using a completely separate type for formula but cant use this in my Input Range, the formula being:

="Shee1!"&(ADDRESS(ROW(A1),COLUMN(A1)))&(ADDRESS((ROW(A1)+COUNTIF(A1:A40,">""")),COLUMN(A1)))

Returning= Sheet!A1:A14 based on the formula it looks at having returned values for 14 of the 40 cells.

Genius!

This solved my problem - I would have NEVER thought of that. Thank you