Excel Index vs Offset / Indirect - I don't understand why bias / indirect acceleration in this case

Question

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

Then I 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 parameters from the first sheet. The user enters a column number (1 to n), and this parameter column is retrieved from the first sheet that will be used in this instance. The instance then performs hundreds of thousands of row calculations using these parameters.

Parameter Sheet

Instance example

My problem is volatility. With a large number of copies, if I use an offset or indirect to get the parameters, any change in any cell in the book causes the parameters to be retrieved again on each sheet of the instance, and therefore each sheet is recounted whole every time, freezing the book for about 3 seconds every time, when a change occurs.

I thought I could reduce this using Index. Each sheet parameter refers to a row on the first sheet containing this parameter, with the column number being pulled from the index. This solved the problem of any changes that caused the recalculation. Now only changes in parameter ranges cause recalculation, but for some reason it is much worse.

Now changing a random cell in a book no longer makes it all freeze for 3 seconds while it is being recalculated, but changing the corresponding cell in the parameter range will lead to recalculation of each sheet and take about 10 seconds. Why is this method much slower. It should technically do the same thing as before, only when the corresponding parameter changes.

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

Solutions

I considered a complete solution that includes VBA, after which a copy of the template instance controls its cell "Instance Number". When it changed, VBA code could copy the corresponding parameters on a worksheet and print the values ​​here. I will also need VBA monitoring of the change event on the main options page. When something changes, he will need to check the column number, iterate over all the patterns and reprogram the values ​​if this number refers. I want to avoid this solution for the usual reasons that VBA goes out of the equation, but may be needed if it is not possible to make excel recalculation smarter about parameter changes.

Answer 1

The problem is caused by using the Index function to populate Param # values ​​on instance sheets.

When you change the value for Param 1 to Main on any line in the instance, the range is marked as changed. Since each sheet of the instance belongs to this range to search for the value of parameter 1, all values ​​of Param 1 are marked as changed. Then, the entire Iteration # formula, which refers to Param 1 on all sheets, is marked as modified.

I don’t know what kind of effects stream will have in your workbook design, but consider changing the index search for direct cell references. for example, in cell 1 of instance D3: =Main!B2

In preparing this answer, I set up a text with a workbook event to report on recalculation of the worksheet

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

I conducted several experiments on various searches using Index, Offset, and others, but could not find one that did not cause the recalculation of all sheets (and not an exhaustive search so that others could offer a solution). The only thing I found that did not call all sheets for calculation was a link to a direct cell

A VBA procedure to help set up or maintain instance sheet parameter references might be ok ...

Answer 2

Here is an old Excel macro trick that still works:

• Disable automatic recalc
• Select all the cells you want to recount.
• Use the replace function to replace = with =
• It does nothing but make these specific cells recount.

It's easy enough to write this as a macro and do it regularly.