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

D Tyme

New Member
Joined
Jan 29, 2025
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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,
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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).
 
Upvote 0
I am having the exact same issue as OP. I'm using excel in SharePoint--I'm using a =COUNTIF formula that automatically updated perfectly in the past and is no longer refreshing as of late, and the formula settings in my workbook are set to "automatic" updates. Every time I change a cell that is used in the formula, I see the correct =COUNTIF answer flash briefly before it goes back to the same number that was there before the update--in essence, it briefly refreshes in a flash but then ultimately does not show the correct refreshed cell. My formula is very simple, and my data set is very small. I only have two tabs in the workbook, and maybe a total of 132 cells that the data is pulling from. So my issue is different from OP's in that I am not using a large dataset. Any idea on how to fix?
 
Upvote 0
I am having the exact same issue. If I copy and paste the formula again then it updates and stays.
 

Attachments

  • 1st try.png
    1st try.png
    13.6 KB · Views: 1
  • after copy and paste.png
    after copy and paste.png
    15.1 KB · Views: 1
Upvote 0

Forum statistics

Threads
1,226,452
Messages
6,191,130
Members
453,641
Latest member
enfkkviesm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top