Formulas Not Updating Automatically in Investing Spreadsheet

[mflatt23]

Hi everyone,

I'm hoping someone can help me figure this out as I've been banging my head against the table on this for the last few days. I have an investing checklist spreadsheet where I have a separate tab for each company that I analyze and then a "Rank" tab where I aggregate all the scores from the individual tabs into a table, with the "Quality Score" column the focus of this post.

Separately, I have a benchmarking workbook for certain metrics like ROIC % ("ROIC" tab). That additional workbook links back to the original checklist spreadsheet as it extracts ROIC % from every company's individual tab and populates it into a pivot table.

What I'm finding out is that often times when I updated the ROIC % on a company's individual tab, that change is not reflected in the benchmarking workbook unless I go to the workbook, click the cell and then hit enter. Additionally, even within the same checklist workbook, when I update a company's score on their tab (Total Score, F26) it does not always reflect the updated score in the "Quality Score" column on the "Rank" tab.

I've searched every possible reason for this error (having manual calculations, cell format is text, quotes in formulas, etc.) and I can't figure it out. What makes it even odder is that sometimes cells update automatically and sometimes they don't.

I'd love to get some guidance on this - I'm linking to the two workbooks in case someone can offer help!

Thank you all.

Link here: https://file.io/VTAr62q2GQJQ

 

[Chrisdontm]

Hello,

I've noticed no one has suggested anything.
Also, the "Link here" is not working.
Here are a few thoughts;
It sounds like your sheets are set to Manual Calculations.

 

[Chrisdontm]

It sounds like your sheets are set to "Manual Calculations", or "Automatic Except for Data Tables".
pressing the "F9" key will calculate every page, and pressing "shift + F9" will calculate only the sheet that you are on.
The setting for that is on the Formulas tab up top, all the way to the right;
click "Calculation Options" and choose: " Automatic"

This could be happening if you have a macro that turns them off, and doesn't turn them back on at the end of the macro, or if the macro gets stopped for some reason and you just close out the macro. ( That way, it never made it to that line of code. )

FYI; It can help a macro to run faster if you turn that off while running the macro.

A macro could be set to run as soon as you click on a page.
If that macro is doing it, then right click the name of the sheet, and choose: " View Code"
Look at the top of the code if it turns off calculations, ( i.e. Application.Calculation = xlCalculationManual ), and around the end, if it turns them back on by setting the same line of code to: ( Application.Calculation = xlCalculationAutomatic ).

If they don't have that last line of code right before the end, you could add that in.

Hopefully something mentioned will help...

 

[mflatt23]

That was actually the first thing I checked with manual calculations. I updated the link expiration so it should be active for another week here: https://file.io/tzxKJWclBk44

I'll take a look at the macros as well, but thanks for any additional help!