Hi all
Browsing through a few threads to find a solution to one problem with our sheets at work, I had an idea about another one but I can't find anything through googling about it.
Lets say I have 2 visible sheets, "Data" and "Report"
"Report" has been built really badly by my predecessor, but I'm not allowed to change it (can I hear you say "frustrating!" I wonder?
) It has close to 50 million calculations (mostly COUNTIFS with 4-8 criteria referencing entire columns on the "Data" sheet against cells on a hidden third worksheet) which run every time the worksheet calculates.
I would like the sheet users to be able to use the Data sheet without the constant 20-30 second delays caused by the Report sheet recalculating, but I need to leave automatic calculations turned on because they do use some formulas on the data sheet as part of their work and when I tried recommending they switch to manual I was met with heavy resistance.
The only way I can think of doing this is to have a macro that each time any sheet other than the "Report" sheet is selected it replaces all the "=" on the "Report" sheet with "@" or "#" or something else harmless that breaks the formulas, and then every time the "Report" sheet itself gets selcted those "=" get put back in and it recalculates that sheet only at that point.
Is this an efficient way to do this or is there a better way? I'm happy enough to experiment with writing the code myself (it's how I learn so I don't want to be spoon-fed), I just want to check I'm not missing some easier way of achieving the same goal before I do so.
Browsing through a few threads to find a solution to one problem with our sheets at work, I had an idea about another one but I can't find anything through googling about it.
Lets say I have 2 visible sheets, "Data" and "Report"
"Report" has been built really badly by my predecessor, but I'm not allowed to change it (can I hear you say "frustrating!" I wonder?
) It has close to 50 million calculations (mostly COUNTIFS with 4-8 criteria referencing entire columns on the "Data" sheet against cells on a hidden third worksheet) which run every time the worksheet calculates.I would like the sheet users to be able to use the Data sheet without the constant 20-30 second delays caused by the Report sheet recalculating, but I need to leave automatic calculations turned on because they do use some formulas on the data sheet as part of their work and when I tried recommending they switch to manual I was met with heavy resistance.
The only way I can think of doing this is to have a macro that each time any sheet other than the "Report" sheet is selected it replaces all the "=" on the "Report" sheet with "@" or "#" or something else harmless that breaks the formulas, and then every time the "Report" sheet itself gets selcted those "=" get put back in and it recalculates that sheet only at that point.
Is this an efficient way to do this or is there a better way? I'm happy enough to experiment with writing the code myself (it's how I learn so I don't want to be spoon-fed), I just want to check I'm not missing some easier way of achieving the same goal before I do so.
