You could use a custom function to return the first worksheet name (and the
lookup range) as a string

Paste into a VBA module:

Function FirstSheetName(Rng As String) As String
FirstSheetName = Worksheets(1).Name

If InStr(1, " ", FirstSheet, vbTextCompare) Then
FirstSheetName = "'" & FirstSheetName & "'" & _
"!" & Rng
Else: FirstSheetName = FirstSheetName & "!" & Rng
End If

End Function


Then try the INDIRECT function in your Vlookup to reference the lookup range.

=Vlookup($A4,Indirect(firstsheetname("$1:655")),38 ,0)


"RichP" wrote:


Hi all,

I want to access some information from the first worksheet of the
workbook. I am currently using =Vlookup($A4,'Sheet 1!$1:655,38,0) and
this works, however the twist is that the worksheets expire and are
replaced by a new one with a different name. I would like to get the
results I am currently achieving but be able to fix the function to
lookup the physical first sheet not to be locked to sheet 1.

I hope someone can help


--
RichP
------------------------------------------------------------------------
RichP's Profile: http://www.excelforum.com/member.php...o&userid=25267
View this thread: http://www.excelforum.com/showthread...hreadid=387620