Calculation Bug?

[ICE9]

There appears to be a bug in Excel which is causing me considerable woes. Maybe it is functioning as intented but i cannot imagine any reason why.

There are tables on two sheets, and each one has VLOOKUP functions and autofilter applied. There are no circular references. There are dynamic named ranges for some columns in each table.

Excel re-calculates the entire workbook every time I make any change. This even occurs if the change is not in a cell referenced by functions in either table. If i change the autofilter, it also recalculates. Since VLOOKUP and the dynamic named ranges are not affected by Autofilter, i can't imagine why it would do this.

I thought this might be normal except it gets worse. If i have this workbook open, and open a new blank workbook, it will re-calculate all workbooks any time a change is made in the new blank workbook.

Why does it recalculate a different workbook, when the new blank workbook is obviously not associated with it? Is this a bug, and is there a workaround? This apparent bug results in a wait of 10 to 15 seconds any time i change a cell.

If the calculation is unecessary, it can sometimes be stoped by clicking in another cell. I don't think this would affect anything since there was nothing new to calculate anyways. Unfortunately, it sometimes still requires the calculation, when no referenced cells have changed.

[Alan]

XL will recalculate every time you hit enter, but unless you are using
volatile formulas like SUMPRODUCT or array entered formulas it doesn't
usually make any noticeable difference assuming the entire file isn't of a
huge size.
Perhaps you could post some of your formulas and give an indication of the
amount of data you have,
Regards,
Alan
"ICE9" <[email protected]> wrote in message
news:[email protected]...
>
> There appears to be a bug in Excel which is causing me considerable
> woes. Maybe it is functioning as intented but i cannot imagine any
> reason why.
>
> There are tables on two sheets, and each one has VLOOKUP functions and
> autofilter applied. There are no circular references. There are
> dynamic named ranges for some columns in each table.
>
> Excel re-calculates the entire workbook every time I make any change.
> This even occurs if the change is not in a cell referenced by functions
> in either table. If i change the autofilter, it also recalculates.
> Since VLOOKUP and the dynamic named ranges are not affected by
> Autofilter, i can't imagine why it would do this.
>
> I thought this might be normal except it gets worse. If i have this
> workbook open, and open a new blank workbook, it will re-calculate all
> workbooks any time a change is made in the new blank workbook.
>
> Why does it recalculate a different workbook, when the new blank
> workbook is obviously not associated with it? Is this a bug, and is
> there a workaround? This apparent bug results in a wait of 10 to 15
> seconds any time i change a cell.
>
> If the calculation is unecessary, it can sometimes be stoped by
> clicking in another cell. I don't think this would affect anything
> since there was nothing new to calculate anyways. Unfortunately, it
> sometimes still requires the calculation, when no referenced cells have
> changed.
>
>
> --
> ICE9
> ------------------------------------------------------------------------
> ICE9's Profile:
> http://www.excelforum.com/member.php...o&userid=13565
> View this thread: http://www.excelforum.com/showthread...hreadid=377098
>

[Biff]

>There are dynamic named ranges for some columns in each table

Are you using an Offset formula in your name formulas?

Offset is a volatile function and will recalc (when some other action
triggers a calc) whether anything changed or not.

Biff

"ICE9" <[email protected]> wrote in message
news:[email protected]...
>
> There appears to be a bug in Excel which is causing me considerable
> woes. Maybe it is functioning as intented but i cannot imagine any
> reason why.
>
> There are tables on two sheets, and each one has VLOOKUP functions and
> autofilter applied. There are no circular references. There are
> dynamic named ranges for some columns in each table.
>
> Excel re-calculates the entire workbook every time I make any change.
> This even occurs if the change is not in a cell referenced by functions
> in either table. If i change the autofilter, it also recalculates.
> Since VLOOKUP and the dynamic named ranges are not affected by
> Autofilter, i can't imagine why it would do this.
>
> I thought this might be normal except it gets worse. If i have this
> workbook open, and open a new blank workbook, it will re-calculate all
> workbooks any time a change is made in the new blank workbook.
>
> Why does it recalculate a different workbook, when the new blank
> workbook is obviously not associated with it? Is this a bug, and is
> there a workaround? This apparent bug results in a wait of 10 to 15
> seconds any time i change a cell.
>
> If the calculation is unecessary, it can sometimes be stoped by
> clicking in another cell. I don't think this would affect anything
> since there was nothing new to calculate anyways. Unfortunately, it
> sometimes still requires the calculation, when no referenced cells have
> changed.
>
>
> --
> ICE9
> ------------------------------------------------------------------------
> ICE9's Profile:
> http://www.excelforum.com/member.php...o&userid=13565
> View this thread: http://www.excelforum.com/showthread...hreadid=377098
>