Auto-adjust formulas
 

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.

Auto-adjust formulas
 

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!*


"se7098" wrote:

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.

Auto-adjust formulas
 

That works! Thanks!

"Luke M" wrote:

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!*


"se7098" wrote:

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.

Auto-adjust formulas
 

Hi,

Highlight B1:B38 and convert it to a List (Data List). Once you do so,
the range becomes auto expanding.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"se7098" wrote in message
...
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.