A
Art Decco
Are formulae that use INDIRECT recalculated *every time*, regardless
of the dependency tree?
Background detail:
This is Excel 2002, on a fast machine with lots of RAM, and a workbook
created with this version of Excel.
I have a spreadsheet that displays "Calculating Cells n%", which takes
7 seconds or so to complete, whenever I make even the most trivial
change. If I find an empty area on a worksheet, type "foo" into an
empty cell, and hit <enter>, it goes through this process and takes so
long (on a fast machine with lots of RAM) that I think it must be
doing a full recalc.
It appears as though the dependency tree mechanism isn't working. If
it were, typing "foo" into a previously empty cell that's not in any
named range or inside a table or any such thing should simply recalc
that one cell and quit. It should be instantaneous.
I'm aware that Excel's dependency tree mechanism shuts down with more
than 65635 dependencies. There may well be more than that number in my
spreadsheet, but what I read is that the word "Calculate" stays in the
status bar when this happens. I'm not getting that. I get the word
"Ready", which switches to "Calculating Cells n%", then at 100% it
switches back to "Ready".
If I hit F9 with an empty cell selected, though, I get the full
7-second "Calculating Cells n%" behavior. I thought F9 by itself (no
modifiers such as ctrl, alt, etc.) was supposed to calculate only the
selected cell and its dependents, not do a full recalc.
However, if I remove just one of the many worksheets in my workbook,
all of this stops. It goes back to nearly instantaneous recalc. That
one sheet is the summary sheet, which contains thousands of cells,
each containing a formula that uses INDIRECT(...) to piece together a
reference to a single cell whose value is to be shown on this summary
page in this cell.
It appears that these INDIRECT() formulae are being recalculated every
time any recalculation is done for any reason anywhere in the workbook
or even, amazingly, when a recalc is done on another totally unrelated
tiny workbook that happens to have been opened while this one is open
in the background.
I can imagine that the recalculation of an indirect formula might take
a long time -- no complaint about that -- so maybe I haven't exceeded
the dependency limit, and I'm not getting a full recalc, but I AM
recalc'ing these INDIRECT formulae every time.
Does anyone know what's going on here?
of the dependency tree?
Background detail:
This is Excel 2002, on a fast machine with lots of RAM, and a workbook
created with this version of Excel.
I have a spreadsheet that displays "Calculating Cells n%", which takes
7 seconds or so to complete, whenever I make even the most trivial
change. If I find an empty area on a worksheet, type "foo" into an
empty cell, and hit <enter>, it goes through this process and takes so
long (on a fast machine with lots of RAM) that I think it must be
doing a full recalc.
It appears as though the dependency tree mechanism isn't working. If
it were, typing "foo" into a previously empty cell that's not in any
named range or inside a table or any such thing should simply recalc
that one cell and quit. It should be instantaneous.
I'm aware that Excel's dependency tree mechanism shuts down with more
than 65635 dependencies. There may well be more than that number in my
spreadsheet, but what I read is that the word "Calculate" stays in the
status bar when this happens. I'm not getting that. I get the word
"Ready", which switches to "Calculating Cells n%", then at 100% it
switches back to "Ready".
If I hit F9 with an empty cell selected, though, I get the full
7-second "Calculating Cells n%" behavior. I thought F9 by itself (no
modifiers such as ctrl, alt, etc.) was supposed to calculate only the
selected cell and its dependents, not do a full recalc.
However, if I remove just one of the many worksheets in my workbook,
all of this stops. It goes back to nearly instantaneous recalc. That
one sheet is the summary sheet, which contains thousands of cells,
each containing a formula that uses INDIRECT(...) to piece together a
reference to a single cell whose value is to be shown on this summary
page in this cell.
It appears that these INDIRECT() formulae are being recalculated every
time any recalculation is done for any reason anywhere in the workbook
or even, amazingly, when a recalc is done on another totally unrelated
tiny workbook that happens to have been opened while this one is open
in the background.
I can imagine that the recalculation of an indirect formula might take
a long time -- no complaint about that -- so maybe I haven't exceeded
the dependency limit, and I'm not getting a full recalc, but I AM
recalc'ing these INDIRECT formulae every time.
Does anyone know what's going on here?
