Auto-adjust formulas

  • Thread starter Thread starter se7098
  • Start date Start date
S

se7098

Is there a way to set up your formula in such a way that when your source
data column is longer or shorter that the formula will automatically detect
that and adjust for it?

example: Column B is 38 lines long this time but when I ran it yesterday it
was 35 lines long so the last 3 lines were not captured in my data today

and i am using 2003 and my formulas do not allow for selecting the entire
column

thanks.
 
Without an example of what your formula looks like, it makes it harder for us
to suggest what you can do.

If you can't simply make the range excessively large, you could use
dynamically named ranges. An example:

Go to Insert - Name.. - Define
Use any name you want (MyRange)
Refers to:
=OFFSET(B1,0,0,COUNTA(B:B),1)
Hit "Add", and then "ok"

Your formula can now reference the named range
=SUM(MyRange)

This formula counts how many non-blank cells are in column B (say 38) and
thus the entire formula returns the range B1:B38.
 
That works! Thanks!

Luke M said:
Without an example of what your formula looks like, it makes it harder for us
to suggest what you can do.

If you can't simply make the range excessively large, you could use
dynamically named ranges. An example:

Go to Insert - Name.. - Define
Use any name you want (MyRange)
Refers to:
=OFFSET(B1,0,0,COUNTA(B:B),1)
Hit "Add", and then "ok"

Your formula can now reference the named range
=SUM(MyRange)

This formula counts how many non-blank cells are in column B (say 38) and
thus the entire formula returns the range B1:B38.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*
Click to expand...
 
Back
Top