Workbook continuously recalculating when unlinked cell is changed???

[martbloke]

Hi all - wondering if someone would be able to assist me.

I have a workbook with formulae in multiple sheets referring to other sheets in the same workbook that are quite basic (SUMIFS, COUNTIFS, IF statements, etc) - we'll call this workbook A. There are dynamic ranges and PivotTables as well which are all within the same workbook - note: a couple of PivotTables are directly linked to an external database but no formula refers to any workbooks outside of workbook A whatsoever; just the data contained within. Calculation is set to automatic which by Excel's definition means that should (and only when) a cell linked to a formula be amended or is affected by a change to the workbook i.e. inserting a column, the formulae will automatically update which is fine by me.

Now I have an issue with this workbook where no matter what I do in another workbook while workbook A is open, workbook A keeps recalculating. Even when just creating a brand new workbook (workbook B) with no formulae in and just typing text in a cell, workbook A recalculates everything each time, which is a pain as it takes a good minute each time to calculate. There is no plausible explanation for this that I can see at all as to why it thinks it should recalculate.

Now this may not seem like such an issue and I know an answer is to switch to manual calculation but I shouldn't have to - workbook A should only recalculate when a linked cell is amended or something is changed that affects a cell that is linked to a formula in workbook A. In my eyes, a cell in a workbook B which is not linked to workbook A and where workbook B doesn't even have any formulae in should not cause a recalculation.

Has anyone else had this issue and/or knows of what could be the cause of this?

 

[offthelip]

It sounds like you have some volatile functions in your workbook, these specific functions are recalculated everytime regardless of whether anything in the dependency chain has changed. This could easily trigger recalculation of the whole workbook. The volatile Excel functions are:
NOW()
TODAY()
RAND()
RANDBETWEEN()
OFFSET()
INDIRECT()
INFO() (depending on its arguments)
CELL() (depending on its arguments)

 

[mole999]

in Options > Formulas, what do you have set for Iterative Calculation

 

[martbloke]

It sounds like you have some volatile functions in your workbook, these specific functions are recalculated everytime regardless of whether anything in the dependency chain has changed. This could easily trigger recalculation of the whole workbook. The volatile Excel functions are:
NOW()
TODAY()
RAND()
RANDBETWEEN()
OFFSET()
INDIRECT()
INFO() (depending on its arguments)
CELL() (depending on its arguments)

So the only formula being used from the above is OFFSET(). I am using this formula in naming a number of dynamic ranges (in the Name Manager) not actually in any cell in the workbook. Do you think it is these that are causing the constant recalculation? I shall have to test this by removing them.

 

[martbloke]

in Options > Formulas, what do you have set for Iterative Calculation

This is not enabled in my options.

 

[martbloke]

It sounds like you have some volatile functions in your workbook, these specific functions are recalculated everytime regardless of whether anything in the dependency chain has changed. This could easily trigger recalculation of the whole workbook. The volatile Excel functions are:
NOW()
TODAY()
RAND()
RANDBETWEEN()
OFFSET()
INDIRECT()
INFO() (depending on its arguments)
CELL() (depending on its arguments)

Turns out after testing this, this was the answer. Thanks for this and this is something I will definitely remember!!

 

[offthelip]

Glad to be of assistance!!