I have a spreadsheet that I have been working on that involves usin
INDIRECT to get the address of the last cell in order to calculat
database formulas. It was originally hardcoded and took about 1 1/
minutes to run. But I kept having to change the formulas. When
changed to indirect so that I wouldn't have to keep changing formulas
it now takes about 5 1/2 minutes to run. I know that everyone says no
to use INDIRECT much, but I don't know another way. Has anyone writte
a non-volatile INDIRECT? Or does anyone have another suggestion?
I would appreciate any help!

--
Message posted from http://www.ExcelForum.com

If you are using INDIRECT or OFFSET to dynamically calculate the address of
the last cell then it has to be volatile for it it to function correctly.

(I always use OFFSET but its mostly a question of taste).

You can save some calculating time by using a helper cell to count the
number of rows once, and then referring to that cell in your dynamic range,
rather than counting the number of rows in every dynamic range.

You may also want to look at my website pages on speeding up calculations.

regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

"aareding " wrote in message
...
I have a spreadsheet that I have been working on that involves using
INDIRECT to get the address of the last cell in order to calculate
database formulas. It was originally hardcoded and took about 1 1/2
minutes to run. But I kept having to change the formulas. When I
changed to indirect so that I wouldn't have to keep changing formulas,
it now takes about 5 1/2 minutes to run. I know that everyone says not
to use INDIRECT much, but I don't know another way. Has anyone written
a non-volatile INDIRECT? Or does anyone have another suggestion?
I would appreciate any help!!


---
Message posted from http://www.ExcelForum.com/

For my purposes, INDIRECT doesn't need to be volatile. I enter all o
my data at the start of the day and it needs to calculate once and the
be used in all of my database formulas

--
Message posted from http://www.ExcelForum.com

So after you have entered all your data enter the last row number in a cell
and refer to that.

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

"aareding " wrote in message
...
For my purposes, INDIRECT doesn't need to be volatile. I enter all of
my data at the start of the day and it needs to calculate once and then
be used in all of my database formulas.


---
Message posted from http://www.ExcelForum.com/