Forum Discussion
Solved
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: ...
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 FunctionUse like this to retrieve the value of cell A1 on the 3rd sheet:
=ByIndex(3,"A1")
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 FunctionUse like this to retrieve the value of cell A1 on the 3rd sheet:
=ByIndex(3,"A1")- 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.
