workbook won't auto-update even though my formula settings are set to "Automatic"

[D Tyme]

As stated, I have formulas that won't update values automatically even though my Formula > Calculation Options are already set to "Automatic".

The values do update, but only after I click the "Calculate Sheet" button. I have a summary tab that refers to about 30 other tabs...... so I have to click "Calculate Sheet" on each tab, again even though my Formula > Calculation Options are already set to "Automatic".

I believe this issue started when I incorporated an array index match formula into my workbook. This array index match formula is useful for pulling needed info, so is there a way I can keep this and also have the auto feature function correctly?

thanks everybody,

 

[Joe4]

Welcome to the Board!

I have seen something like this happen many years ago. I don't know if it is still the case but I will tell you what happened.

At one point, Excel could only track something like 1 million calculation dependencies. If you exceeded that number, it would not auto-calculate updates, or at least it would take a real long time, as it would have to recalculate every single value (since it cannot track dependencies anymore).

If you have 30 tabs, with a lot of index/match formulas, that probably means you are using the wrong tool for the job.
What that describes is a relational database. While people can "brute force" Excel into acting like a relational database, it is often clunky and inefficient, and you can have issues like you are seeing. Excel was not designed to be a relational database program. Programs like Microsoft Access, SQL, and Oracle are.

If you have no other choice, and have to use Excel like this, I would recommend looking at Power Query, which is an add-on tool to Excel that allows it to function more like a database.
There is a forum for that here: Power Tools

Also note that Excel 365 has some new functions that may make some of what you may be trying to do easier, such as the new FILTER function (see: FILTER function - Microsoft Support).