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

[Bernd13505]

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.

[#2 · Accepted solution]

@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")

[#3]

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.