Dynamic Range Based on Cell Result not Empty Cell

  • Thread starter Thread starter ExcelMonkey
  • Start date Start date
E

ExcelMonkey

I use dynamic ranges in excel. I use them to name ranges that vary i
size due to varyin data. For example, I may dump data in F2 which ma
span 3 rows or 100 rows. I use the following formula to name th
range:

=OFFSET($F$2,0,0,COUNTA($F2:$F65536),1)

However, I now want to define the range based on the cell result. Tha
is assume that I have a constant formula from F2 to F102. However th
cells in the column either have a number in them or a "" in them. Th
formula above will automatically size the range to F2:F102 becuas
there is a formula in every row. How can i write a formual which wil
size the range based on a formulas result?

Not that the numbers will all be back to back. They will not b
intermittant.

Thank
 
Hey this works fine!! Just out of curiosity, how is this working?
What does the 9.99999999999999E307 represent?


=OFFSET($F$2,0,0,MATCH(9.99999999999999E307,$F2:$F65536),1)

Thank
 
Hi
this is nearly the maximum numeric value Excel can process. So it looks
in your column and returns the last numeric value which is smaller or
equal than this large number. So in all realistic cases this returns
the last NUMERIC value in a column
 
Back
Top