Hi all,

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

I hope someone can hel

--
Rich
-----------------------------------------------------------------------
RichP's Profile: http://www.excelforum.com/member.php...fo&userid=2526
View this thread: http://www.excelforum.com/showthread.php?threadid=38762

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

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

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

Thanks for all the help it has worked a treat. I am now trying to break
the code down to understand what each part does.
Can the sheet name be displayed in a cell in the workbook?


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

you can make the Rng argument optional and test to see if it is present. If
missing, return just the sheet name and, if not, return a string reference
that includes the sheet name and the range reference.

Also, you could make the function more generic by adding an additional
argument (the number of the sheet whose name you want returned). Then you
can return the sheet name for any sheet, not just the first one.

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.




"RichP" wrote:


Thanks for all the help it has worked a treat. I am now trying to break
the code down to understand what each part does.
Can the sheet name be displayed in a cell in the workbook?


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

This code also works well and has proved most useful :

--
Rich
-----------------------------------------------------------------------
RichP's Profile: http://www.excelforum.com/member.php...fo&userid=2526
View this thread: http://www.excelforum.com/showthread.php?threadid=38762

That's good to hear!


"RichP" wrote:


This code also works well and has proved most useful :)


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

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.

The code in the function worked well until now. I have taken out the
original first sheet and I believe that it looks for this sheet, I
don't really know.

When executing the following line an error occurs and the program seems
to be looping continuously through the function.

The code is Sheets(Numsheets-1).copy Befo=Sheets(Numsheets-1)
'Where Numsheets = worksheets.count

Which is supposed to copy the last but one sheet and paste the new
sheet just before the last one, As I said this all worked fine until I
moved the original sheet.

I hope this makes sence and 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

Hi,

I don't know what is happening on your workbook actually but try this:

Sub Test()
Dim Numsheets As Integer
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Numsheets = Worksheets.Count
Sheets(Numsheets - 1).Copy Befo=Sheets(Numsheets - 1)
Application.EnableEvents = True
End Sub

'Run this after the above test.
Sub RestoreSettings()
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub

if something changes, then there is a possibility that calculation of
your formula or some event procedures affect the macro.
I guess there might be a circular reference after copying the sheet.

--
HTH,

okaizawa


RichP wrote:
The code in the function worked well until now. I have taken out the
original first sheet and I believe that it looks for this sheet, I
don't really know.

When executing the following line an error occurs and the program seems
to be looping continuously through the function.

The code is Sheets(Numsheets-1).copy Befo=Sheets(Numsheets-1)
'Where Numsheets = worksheets.count

Which is supposed to copy the last but one sheet and paste the new
sheet just before the last one, As I said this all worked fine until I
moved the original sheet.

I hope this makes sence and someone can help