Excel Index vs Offset/Indirect - I don't understand why Offset/Indirect is faster in this case

Question

I have a workbook set up where the first tab contains a list of parameters. Each column is a set of parameters for a different instance.

I then have a template sheet for one instance, and the user makes many copies of this template. One of the things the template does is dynamically pull over the parameters from the first sheet. The user enters a column number (1 to n) and that column of parameters is pulled from the first sheet to be used on that instance. The instance then has runs hundreds of thousands of rows calculations using those parameters.

Parameters Sheet

Sample Instance

My problem appears to be related to volatility. With a large number of instances, if I use offset or indirect to get the parameters, any change in any cell in the workbook causes the parameters to be re-retrieved on each instance sheet, and so each sheet recalculates in its entirety every time, freezing the workbook for about 3 seconds whenever a change is made.

I thought I could lessen this by using Index. Each sheet's parameters reference the row on the first sheet containing that parameter, with the column number to pull from as the index parameter. This solved the problem of any change causing a recalculation. Now only changes in the parameter ranges cause a recalculation, but for some reason, it's much much worse.

Now, changing a random cell in the workbook no longer causes the whole thing to freeze for 3 seconds while it recalculates, but changing a relevant cell in the parameters range will cause each sheet to recalculate, and take about 10 seconds. Why is this method so much slower. It should technically be doing the same thing as before, just only when a relevant parameter is changed.

Is there any way to set this up so that when a parameter on the front sheet is changed, only the sheets that are affected by that parameter recalculate?

Solutions

I've considered a complex solution involving VBA, whereupon copying the template instance monitors its "Instance Number" cell. When it's changed, VBA code could copy the corresponding parameters to the sheet and hard copy the values here. I would also need VBA monitoring the change event on the main parameters sheet. When anything is changed, it would have to check the column number, iterate over all templates, and re-copying the values if that instance number is referenced. I want to avoid this solution for the usual reasons of keeping VBA out of the equation, but it might be necessary if there's no way to make excel recalculation smarter about changes to parameters.

Answer 1

The issue is caused by using Index function to populate Param # values onto the Instance sheets.

When you change a value for Param 1 in Main on any Instance row, the range is marked as changed. Since every Instance sheet refers to this range for its parameter 1 value lookup, all param 1 values are marked as changed. Then all Iteration # formula that refer to Param 1 on all sheets are marked as changed.

Don't know what flow on effects this would have on you workbook design, but consider changing Index lookup to direct cell references. eg on Instance 1 cell D3: =Main!B2

In preparing this answer I set up a text with a workbook event to report sheet recalulations

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    MsgBox "Calc Sheet " & Sh.Name
End Sub

I did some experiments on various lookups using Index, Offset and others but could not find one that did not cause all sheets to recalculate (not an exhaustive search, so others may be able to offer a solution). The only one I did find that did not cause all sheets to calculate was the direct cell reference

A VBA routine to help set up or maintain the Instance sheets parameter references may be in order...

Answer 2

Here's an old Excel macro trick that still works:

• Turn off automatic recalc
• Select all the cells that you want to recalculate
• Use the replace function to replace = with =
• This does nothing but force those particular cells to recalculate.

It's fairly easy to record this as a macro and do it regularly.