Replace =INDIRECT

**BRISBANEBOB** · 12-23-2011, 07:13 PM

Attached is an extract from a 50+ sheet spreadsheet. It allows basic Excel users to record information and this information is summarised on one sheet using =INDIRECT. When fully populated it is around 20 mb and this can take upwards of 15 minutes to recalculate.

By using different formula, can I achieve a faster recalc method which allows the summary sheet to get automatically updated after the users have entered their data? I do not really want to resort to code or asking the users to write formula themselves.

Thanks for any advice or ideas

**DonkeyOte** · 12-25-2011, 04:44 AM

A few people have looked at this file and I would say most have probably arrived at the same conclusion... there is nothing in the attachment that would warrant 15 min calc time. The INDIRECTs, though volatile, are not expensive calculations.

So... on that basis:

Do you have any other calculations in this file other than those in the sample ? If so, please provide an example ... performance killers are generally SUMPRODUCT & Arrays (volatile precedents don't help obviously)
Are you using Dynamic Named Ranges in your main file ? If so, how are these a) defined b) utilised
Are you using Conditional Formatting ? (other than that disclosed within the sample)

If you don't really need the sheet name in A1 on each SCR sheet you might choose to remove the CELL based calcs (volatile) though again I doubt this will make a huge difference.

Going back to your original question: yes, VBA is certainly a possibility, however, first I would suggest determining why you need to ... may be other bigger fishes to fry in the first instance.

edit:

I didn't notice you have Conditional Format rules on the SCR sheets - if you have 500 of these sheets then performance will take a hit. To benchmark remove the Conditional Formats and review performance.

**BRISBANEBOB** · 12-26-2011, 05:20 PM

The only volatile formula I am using is the INDIRECT. There is some, but not a lot, of conditional formatting. It probably just the size of the file and I suspect there is nothing that can be done other than to resort to code (as an accountant, I write the worst code in the world).

As a matter of interest, does conditional formatting have a big impact on speed?

Thanks for taking the time to respond.

**DonkeyOte** · 12-26-2011, 05:27 PM

Conditional Formatting is super-volatile - see Volatile sig. link for more info.

For the above reason 500+ sheets with Conditional Formats is likely to have a detrimental impact on performance given associated calculation overhead.