Hi,

in a standard module, 'Worksheets(SheetIndex)' means a sheet
in the active workbook at the time of calculation.
it might be a different sheet from the expected.
i think this is better,

Function FirstSheet()
FirstSheet = Application.Caller.Worksheet.Parent.Worksheets(1). Name
End Function

=INDIRECT("'"&FirstSheet()&"'!A1")

--
HTH,
okaizawa


JMB wrote:

Function SheetName(SheetIndex As Long, Optional Rng As Variant) As String
If IsMissing(Rng) Then
SheetName = Worksheets(SheetIndex).Name
ElseIf InStr(1, Worksheets(SheetIndex).Name, " ", vbTextCompare) 0 Then
SheetName = "'" & Worksheets(SheetIndex).Name & "'" & _
"!" & Rng
Else: SheetName = Worksheets(SheetIndex).Name & "!" & Rng
End If

End Function


=sheetname(1,"$A$5:$B$6")
should return something like Sheet1!$A$5:$B$6
or 'Sheet 1'!$A$5:$B$6 if there is a space in the sheet name

=sheetname(1)
should return Sheet1 (name only w/o apostrophes or exclamation mark.