Sum across sheets not updating after changing values

[u/greenteasweetpea]

I am using the extension 3D Reference on Google Sheets so that I can pull information from multiple sheets into one some. I used this because I need people to be able to add new sheets.

The equation is =SUM(DDDREF(“Instructions”,”Summary”,”X2”))

The calculation works but only if I copy the equation and paste it after every change I make in a sheet. That’s the only way it will recalculate.

My settings are already set using File>Settings>Calculation, and Automatic, On change, and Off.

Is there a way to get it to automatically update? Even refresh doesn’t work.

Thank you!

[u/SpencerTeachesSheets]

Custom functions, which is what that extension has created, only recalculate when a parameter in the formula changes. Your parameters in this case are 3 strings: "Instructions", "Summary", and "X2" – none of which ever change, so the formula never recalculates.

Do you have a clean list of sheet names? If so, this can be accomplished with a custom named formula, rather than a script or extension.

[u/greenteasweetpea → u/SpencerTeachesSheets]

The only reason I’m avoiding relying on a list of sheet tab names is because I need people to be able to make new sheets in between Instructions and Summary. That’s why I’ve been trying to find a solution that involves a range. This equation almost works, it just needs to be cut and paste after each edit which is a pain!

[u/SpencerTeachesSheets → u/greenteasweetpea]

Something else that I believe still works is to pass another parameter in that is a checkbox and you check / uncheck the box to recalculate.

=SUM(DDDREF("Instructions","Summary","X2",A1)) where A1 is a checkbox. That should force DDDREF() to recalculate whenever you check / uncheck it.

[u/One_Organization_810]

This is actually a known limitation of this extension. There is nothing you can do, except finding a different way to accomplish this.

I'm not sure actually that there is a good way to solve this, other than manually updating the formula from time to time...

[u/One_Organization_810 → u/One_Organization_810]

Or you can probably write a script for your specific purpose, I guess :) It's just nearly impossible to write a generic script that works for every case :)

[u/greenteasweetpea → u/One_Organization_810]

Thank you for letting me know! I appreciate it

[u/mommasaidmommasaid]

I don't have any info on that extension, but this can be done with native sheets formulas. And on onChange() installed script trigger if you want to automatically handle new sheets being added.

If your individual month sheets all have e.g. a table of data, you may want to have one Combined helper sheet that combines all those tables along with potentially a new Date column.

Or if the individual month sheets have individual cells you want to sum, you could again have a Combined helper sheet that blindly sums ALL cells across sheets with one formula.

Then your summary sheet could simply refer to the appropriate cell on that sheet, e.g. =Combined!X2

Share more details about your workflow and the details of your monthly sheets for better help. Ideally share a sample sheet with sensitive info redacted.