Excel: Replace INDIRECT() to dynamically refer to

Question

I have a sheet with lots of INDIRECT() functions to pull data from other sheets in the same workbook. Something like

=INDIRECT(SheetName&"!A1")

I've realized that INDIRECT() is a volatile function so it re-calculates every time when any change is made in any workbook. This makes Excel very slow especially when there are several workbooks opened.

Is there any other function that could replace INDIRECT() to lookup with dynamic sheet name?

Answer 1

Lisa: You can use CHOOSE in association with a lookup table to replace INDIRECT. See the screenshot in my answer to this question: Excel Vlookup with cell reference

Also, check out my answer at Excel tables vs plain data where I give a bit of info on how you can address the root cause of your slow spreadsheet.

Answer 2

• Turn of automatic calculculations (strongly not advisable)
• Build a UDF function, that is non-volatile by default:

---

Public Function IndirectNotVolatile(sheetName As String, sheetRange As Range) as Variant
    Set IndirectNotVolatile = Worksheets(sheetName).Range(sheetRange.Address)
End Function