Indirect sheet reference in a cell

[velvetlady]

Is there a way to write an equation to use the values from a specific cell on
another worksheet without using the worksheet name. i.e. reference it by
position in the workbook, such as "Sheet(1)" as is done in Visual Basic?

 

[Gary''s Student]

First enter this UDF:

Function sheetname(i As Integer) As String
sheetname = Sheets(i).Name & "!"
End Function

and then:

=INDIRECT(sheetname(1) & "A9") will return the value in cell A9 in the first
worksheet.