CODEWITHSUNDEEP
excelexcel-formula
Lisa
Lisa

Reputation: 552

Excel: Replace INDIRECT() to dynamically refer to worksheets

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?

Upvotes: 2

Views: 14133

Answers (2)

Vityata
Vityata

Reputation: 43565

  • Turn off automatic calculations (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

Upvotes: 1

jeffreyweir
jeffreyweir

Reputation: 4824

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.

Upvotes: -1

Related Questions