Forum Discussion
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:
I named a worksheet to 'Pos.3' and moved that sheet on the third position in the workbook.
With =INDEX('Pos.3'!A1;TRUE) I would get the value 3 as it is placed on the 3rd position.
Now I want to address the same cell by not using the sheet name 'Pos.3' but instead by using its index 3,
similar to a functionality like =INDIRECT('[index=3]'!A1;TRUE) -which does not exist- and to recieve the value of the cell 'Pos.3'!A1.
And further, if I would move that sheet 'Pos.3' to the fourth position in that workbook, meaning that there is another sheet on position 3 now, for example a sheed named 'xyz', the same function =INDIRECT('[index=3]'!A1;TRUE) would now return the value of the cell 'xyz'!A1, because it is on the 3rd position in the workbook.
My last questions to this topic remained unanswered and I do hope that someone could help me now. Thanks a lot for any helpful reply.
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")
2 Replies
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.
