Prev: linking a sales receipt worksheet to an inventory worksheet
Next: Hour function equivelent help
From: Thanks on 25 May 2010 16:20
I have a large number of array formulae in my sheet. I update the supporting
data and the formulae do not recalculate. The only way i can get a recalc is
to F2 and then ctrl+shift+enter. How can I get all of the formulae to
recalc. Manual recalc does not work.

From: Dave Peterson on 25 May 2010 16:53
Does Automatic Calculation mode work?

If excel is confused about whether it should recalculate a formula, this
sometimes wakes excel up and stops the confusion.

Select all the cells on that sheet
(ctrl-a a few times should do it)

Edit|replace
what: = (equal sign)
with: =
replace all

Excel will see that you're changing all your formulas and know to recalc each
because of the change.

Thanks wrote:
>
> I have a large number of array formulae in my sheet. I update the supporting
> data and the formulae do not recalculate. The only way i can get a recalc is
> to F2 and then ctrl+shift+enter. How can I get all of the formulae to
> recalc. Manual recalc does not work.

--

Dave Peterson
From: Charles Williams on 26 May 2010 04:44

You could try
Ctrl-Alt-F9 to force a Full calculation

or if the dependency tree is messed up you can rebuild it using
ctrl-shift-alt-F9 - sometimes after this a normal F9 will work.

If this does not work you may have hit one of the array formulae
limits.

For excel versions prior to Excel 2007 there is a limit to the number
of array formulae that can refer to other sheets.

from KB 166342

Maximum array formulas
In Excel 2003 and in earlier versions of Excel, a single worksheet may
contain a maximum of 65,472 array formulas that refer to another
worksheet. If you want to use more formulas, split the data into
multiple worksheets so that there are fewer than 65,472 references to
a single worksheet.

For example, in Sheet1 of a workbook, you can create the following
items:

* 65,472 array formulas that refer to Sheet2
* 65,472 array formulas that refer to Sheet3
* 65,472 array formulas that refer to Sheet4

(If you are using multi-cell array formulae I suspect that the limit
is actually 65472 cells)

regards
Charles

>I have a large number of array formulae in my sheet. I update the supporting
>data and the formulae do not recalculate. The only way i can get a recalc is
>to F2 and then ctrl+shift+enter. How can I get all of the formulae to
>recalc. Manual recalc does not work.
 | 
Pages: 1
Prev: linking a sales receipt worksheet to an inventory worksheet
Next: Hour function equivelent help