Currently I have a sheet where I enter my data and then another sheet within
the same workbook that makes various calculations based on this data. But
every time I enter data, it takes a long time for my formulas to calculate,
upwards of a minute. I am pretty sure this is because my formulas contain
entire columns as ranges (i.e. A:A rather than A1:A100). However I need to
use A:A in my formulas as my data sheet is indefinitely long and I will
continue to add to this over the years. I don't want to have to go back and
change all my formulas to A1:A200 every time my data exceeds the range. Is
there anyway around this so that excel only calculates my formulas based on
cells that have an entry? Or any other way to make this faster?

Hi

Assuming there are no empty cells in the range you can use this in a helper
cell ie B1:

="A1:A" & COUNT(A:A)

Then you can use a INDIRECT formula like this:

=SUM(INDIRECT(B1))

Hopes this helps.
....
Per

"yowzers" skrev i meddelelsen
...
Currently I have a sheet where I enter my data and then another sheet
within
the same workbook that makes various calculations based on this data. But
every time I enter data, it takes a long time for my formulas to
calculate,
upwards of a minute. I am pretty sure this is because my formulas contain
entire columns as ranges (i.e. A:A rather than A1:A100). However I need
to
use A:A in my formulas as my data sheet is indefinitely long and I will
continue to add to this over the years. I don't want to have to go back
and
change all my formulas to A1:A200 every time my data exceeds the range.
Is
there anyway around this so that excel only calculates my formulas based
on
cells that have an entry? Or any other way to make this faster?

I am pretty sure this is because my formulas
contain entire columns as ranges
(i.e. A:A rather than A1:A100).

Yeah, that'll add to calculation time if you're using lots of array formulas
(including SUMPRODUCT).

An array formula (including SUMPRODUCT) will evaluate *every* cell in the
referenced range whether it's within the used range or not.

Use dynamic ranges:

http://contextures.com/xlNames01.html#Dynamic

See this for excellent advise/tips on improving efficiency:

http://www.decisionmodels.com/

--
Biff
Microsoft Excel MVP


"yowzers" wrote in message
...
Currently I have a sheet where I enter my data and then another sheet
within
the same workbook that makes various calculations based on this data. But
every time I enter data, it takes a long time for my formulas to
calculate,
upwards of a minute. I am pretty sure this is because my formulas contain
entire columns as ranges (i.e. A:A rather than A1:A100). However I need
to
use A:A in my formulas as my data sheet is indefinitely long and I will
continue to add to this over the years. I don't want to have to go back
and
change all my formulas to A1:A200 every time my data exceeds the range.
Is
there anyway around this so that excel only calculates my formulas based
on
cells that have an entry? Or any other way to make this faster?

Slow Now; Speed UP:
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
.... ... ...
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

Or, if you want to make a Macro fire specifically on one sheet in a workbook:
sheets("Sheet1").activate
Range("A1:Z100").Select
This will take you to the desired sheet for the range selected.

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"yowzers" wrote:

Currently I have a sheet where I enter my data and then another sheet within
the same workbook that makes various calculations based on this data. But
every time I enter data, it takes a long time for my formulas to calculate,
upwards of a minute. I am pretty sure this is because my formulas contain
entire columns as ranges (i.e. A:A rather than A1:A100). However I need to
use A:A in my formulas as my data sheet is indefinitely long and I will
continue to add to this over the years. I don't want to have to go back and
change all my formulas to A1:A200 every time my data exceeds the range. Is
there anyway around this so that excel only calculates my formulas based on
cells that have an entry? Or any other way to make this faster?

Just my 2 cents...

When calc time is already not acceptable the last thing you want to do is
use volatile functions.

You can create dynamic ranges in most cases without using volatile
functions.

="A1:A" & COUNT(A:A)
=SUM(INDIRECT(B1))

Rng refers to:
=A1:INDEX(A:A,COUNT(A:A))

=SUM(Rng)

--
Biff
Microsoft Excel MVP


"Per Jessen" wrote in message
...
Hi

Assuming there are no empty cells in the range you can use this in a
helper cell ie B1:

="A1:A" & COUNT(A:A)

Then you can use a INDIRECT formula like this:

=SUM(INDIRECT(B1))

Hopes this helps.
...
Per

"yowzers" skrev i meddelelsen
...
Currently I have a sheet where I enter my data and then another sheet
within
the same workbook that makes various calculations based on this data.
But
every time I enter data, it takes a long time for my formulas to
calculate,
upwards of a minute. I am pretty sure this is because my formulas
contain
entire columns as ranges (i.e. A:A rather than A1:A100). However I need
to
use A:A in my formulas as my data sheet is indefinitely long and I will
continue to add to this over the years. I don't want to have to go back
and
change all my formulas to A1:A200 every time my data exceeds the range.
Is
there anyway around this so that excel only calculates my formulas based
on
cells that have an entry? Or any other way to make this faster?

Sorry I am a newbie. I have no idea what this means...

"ryguy7272" wrote:

Slow Now; Speed UP:
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
... ... ...
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

Or, if you want to make a Macro fire specifically on one sheet in a workbook:
sheets("Sheet1").activate
Range("A1:Z100").Select
This will take you to the desired sheet for the range selected.

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"yowzers" wrote:

Currently I have a sheet where I enter my data and then another sheet within
the same workbook that makes various calculations based on this data. But
every time I enter data, it takes a long time for my formulas to calculate,
upwards of a minute. I am pretty sure this is because my formulas contain
entire columns as ranges (i.e. A:A rather than A1:A100). However I need to
use A:A in my formulas as my data sheet is indefinitely long and I will
continue to add to this over the years. I don't want to have to go back and
change all my formulas to A1:A200 every time my data exceeds the range. Is
there anyway around this so that excel only calculates my formulas based on
cells that have an entry? Or any other way to make this faster?

Perfect, thanks!

"T. Valko" wrote:

I am pretty sure this is because my formulas
contain entire columns as ranges
(i.e. A:A rather than A1:A100).

Yeah, that'll add to calculation time if you're using lots of array formulas
(including SUMPRODUCT).

An array formula (including SUMPRODUCT) will evaluate *every* cell in the
referenced range whether it's within the used range or not.

Use dynamic ranges:

http://contextures.com/xlNames01.html#Dynamic

See this for excellent advise/tips on improving efficiency:

http://www.decisionmodels.com/

--
Biff
Microsoft Excel MVP


"yowzers" wrote in message
...
Currently I have a sheet where I enter my data and then another sheet
within
the same workbook that makes various calculations based on this data. But
every time I enter data, it takes a long time for my formulas to
calculate,
upwards of a minute. I am pretty sure this is because my formulas contain
entire columns as ranges (i.e. A:A rather than A1:A100). However I need
to
use A:A in my formulas as my data sheet is indefinitely long and I will
continue to add to this over the years. I don't want to have to go back
and
change all my formulas to A1:A200 every time my data exceeds the range.
Is
there anyway around this so that excel only calculates my formulas based
on
cells that have an entry? Or any other way to make this faster?


.

You're welcome!

--
Biff
Microsoft Excel MVP


"yowzers" wrote in message
...
Perfect, thanks!

"T. Valko" wrote:

I am pretty sure this is because my formulas
contain entire columns as ranges
(i.e. A:A rather than A1:A100).

Yeah, that'll add to calculation time if you're using lots of array
formulas
(including SUMPRODUCT).

An array formula (including SUMPRODUCT) will evaluate *every* cell in the
referenced range whether it's within the used range or not.

Use dynamic ranges:

http://contextures.com/xlNames01.html#Dynamic

See this for excellent advise/tips on improving efficiency:

http://www.decisionmodels.com/

--
Biff
Microsoft Excel MVP


"yowzers" wrote in message
...
Currently I have a sheet where I enter my data and then another sheet
within
the same workbook that makes various calculations based on this data.
But
every time I enter data, it takes a long time for my formulas to
calculate,
upwards of a minute. I am pretty sure this is because my formulas
contain
entire columns as ranges (i.e. A:A rather than A1:A100). However I
need
to
use A:A in my formulas as my data sheet is indefinitely long and I will
continue to add to this over the years. I don't want to have to go
back
and
change all my formulas to A1:A200 every time my data exceeds the range.
Is
there anyway around this so that excel only calculates my formulas
based
on
cells that have an entry? Or any other way to make this faster?


.

Hi,

Your range in the formula should be A1:A100. To make the range auto
expanding, convert it to a List (or Table in Excel 2007) - Ctrl+L

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"yowzers" wrote in message
...
Currently I have a sheet where I enter my data and then another sheet
within
the same workbook that makes various calculations based on this data. But
every time I enter data, it takes a long time for my formulas to
calculate,
upwards of a minute. I am pretty sure this is because my formulas contain
entire columns as ranges (i.e. A:A rather than A1:A100). However I need
to
use A:A in my formulas as my data sheet is indefinitely long and I will
continue to add to this over the years. I don't want to have to go back
and
change all my formulas to A1:A200 every time my data exceeds the range.
Is
there anyway around this so that excel only calculates my formulas based
on
cells that have an entry? Or any other way to make this faster?