Macro alternative to volatile indirect

[jason2483]

I have created a variety of template tools that I use to common size data from different sources. I keep them as a unique workbook and then copy the sheet to the new workbook for calculation. To the side, I have a column where I enter in the names of the sheets to use as source and the indirect function calculates everything flawlessly every time. It works... Unfortunately, the CPU load is driving me out of my mind on my laptop. I have to wait ages after every time I open the tool, move the tool, change the names, etc.

Is there an alternative formula that can apply the sheet name after I change it? If not, is a macro my only option?

Two sample formulas from cells. Cell A10 contains my sheet name reference in this tool.

=IF(ISBLANK(INDIRECT("'"&$A$10&"'"&"!"&"R"&C5)),"",INDIRECT("'"&$A$10&"'"&"!"&"R"&C5))
=INDIRECT("'"&$A$10&"'"&"!"&"H"&C5)

My other tool has two companion lookup sheets that go with it for referencing...I've tried keeping the names the same, but whenever I move the sheets or the tool it likes to reference the hard drive source and cause problems.

Any ideas?

 

[cerfani]

What about changing calculation to manual when working on that file? You can always program a sheet change event if you want to calculate and paste values when you edit some input cell.