Dynamic Range in Excel that won't count formulas

[conorfinnegan]

I have an excel file that I constantly have to update the the ranges
for some of my formulas because sometimes the data fills A1:A35 or
sometimes can go down to A1:45. There are formulas that pull data
from other sheets in cells A1:A45 (if there is data). I would like the
range to only include those cell values that actually have values and
not the remaining formulas that return blanks.

How do I do this?

Thanks in advance for your help.

Conor

 

[T. Valko]

Assuming that the blank cells are all at the bottom of the range:

=OFFSET(A1,,,SUMPRODUCT(--(LEN(A1:A100)>0)))