Syntax for using sheet index number in dynamic references with INDIRECT()

Dear community, how is the syntax when using =INDIRECT() if I want to refer to a cell in another sheet, if I want to use the index number of that sheet instead of the 'given sheet name' ? Example: ...

Formulas and Functions

HansVogelaar
Apr 14, 2022
Bernd13505

There is no built-in way to do that, but you could create a custom VBA function:

Function ByIndex(index As Long, cellref As String) As Range Application.Volatile Set ByIndex = Worksheets(index).Range(cellref) End Function

Use like this to retrieve the value of cell A1 on the 3rd sheet:

=ByIndex(3,"A1")

HansVogelaar

MVP

Apr 14, 2022

Bernd13505

There is no built-in way to do that, but you could create a custom VBA function:

Function ByIndex(index As Long, cellref As String) As Range
    Application.Volatile
    Set ByIndex = Worksheets(index).Range(cellref)
End Function

Use like this to retrieve the value of cell A1 on the 3rd sheet:

=ByIndex(3,"A1")

Bernd13505

Copper Contributor

Apr 14, 2022

Thank you very much, Hans.
It's of course a pity that there is no formula or function in Excel enabling that result without VBA.

Forum Discussion

Syntax for using sheet index number in dynamic references with INDIRECT()

Resources