Yes, I did transpose those arguments. I also left off the test "0".
Apparently it did not mind these mistakes because I used "FirstSheet" instead
of "FirstSheetName" in the InStr function (smack my hands w/a ruler for not
using option explicit).

I think this will work better:


Function FirstSheetName(Rng As String) As String
If InStr(1, Worksheets(1).Name, " ", vbTextCompare) 0 Then
FirstSheetName = "'" & Worksheets(1).Name & "'" & _
"!" & Rng
Else: FirstSheetName = Worksheets(1).Name & "!" & Rng
End If

End Function


Thanks Norman.


"Norman Jones" wrote:

Hi JMB,

If InStr(1, " ", FirstSheet, vbTextCompare) Then

Should be:

If InStr(1, FirstSheetName, " ", vbTextCompare) Then

(the 2nd and 3rd InStr arguments are transposed).


Rather than rectifying, why not drop the InStr test and replace:

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

with

FirstSheetName = "'" & FirstSheetName & "'" & "!" & Rng

which will work for sheet names with, or without, spaces


---
Regards,
Norman



"JMB" wrote in message
...
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