sheet names

  • Thread starter Thread starter John
  • Start date Start date
J

John

What returns the name of a worksheet in a workbook if you are in the
workbook.

for instance: findit(1) gives the name of sheet 1.
Thanks
John
 
John said:
What returns the name of a worksheet in a workbook
if you are in the workbook.
for instance: findit(1) gives the name of sheet 1.
Click to expand...

Try this technique, from a post by Harlan

Click Insert > Name > Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")
))+1,32)
Click OK

The above defines WSN as a name we can use to refer to the sheetname in
formulas, etc. It will auto-extract the sheetname implicitly.
To test it, just enter in any cell in any sheet*: =WSN
and the name of that sheet will be returned

*Note: Workbook must be saved for the above to work, ie a name given to book

---
 
Amazing! Thank you. It seems strange excel doesn't have something to do
this.

John

John said:
What returns the name of a worksheet in a workbook
if you are in the workbook.
for instance: findit(1) gives the name of sheet 1.
Click to expand...


Try this technique, from a post by Harlan

Click Insert > Name > Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")
))+1,32)
Click OK

The above defines WSN as a name we can use to refer to the sheetname in
formulas, etc. It will auto-extract the sheetname implicitly.
To test it, just enter in any cell in any sheet*: =WSN
and the name of that sheet will be returned

*Note: Workbook must be saved for the above to work, ie a name given to book
Click to expand...
 
'Cos 99% of users can just read the sheet name.
anyway no need to have a function for everything that can be provided
using the existing tools/functions :)

Steve


Amazing! Thank you. It seems strange excel doesn't have something to do
this.

John

John said:
What returns the name of a worksheet in a workbook if you are in the
workbook.
for instance: findit(1) gives the name of sheet 1.
Click to expand...
Try this technique, from a post by Harlan
Click Insert > Name > Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")
))+1,32)
Click OK
The above defines WSN as a name we can use to refer to the sheetname
in formulas, etc. It will auto-extract the sheetname implicitly. To
test it, just enter in any cell in any sheet*: =WSN
and the name of that sheet will be returned
*Note: Workbook must be saved for the above to work, ie a name given
to book
Click to expand...
Click to expand...
 
I am writing macros. I have about 150-200 sheets. I want to either find
something or insert something by sheet name in several of my programs.
Apparently there's no way to do it in existing excel functions. It would
seem fundemental to me to be able to find out the name of, say, sheet 42
in my program.

John
'Cos 99% of users can just read the sheet name.
anyway no need to have a function for everything that can be provided
using the existing tools/functions :)

Steve


Amazing! Thank you. It seems strange excel doesn't have something to
do this.

John

:

What returns the name of a worksheet in a workbook if you are in
the workbook.
for instance: findit(1) gives the name of sheet 1.

Try this technique, from a post by Harlan
Click Insert > Name > Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")
))+1,32)
Click OK
The above defines WSN as a name we can use to refer to the
sheetname in formulas, etc. It will auto-extract the sheetname
implicitly. To test it, just enter in any cell in any sheet*: =WSN
and the name of that sheet will be returned
*Note: Workbook must be saved for the above to work, ie a name
given to book
Click to expand...
Click to expand...
Click to expand...
 
John said:
I am writing macros. I have about 150-200 sheets. I want to either find
something or insert something by sheet name in several of my programs.
Apparently there's no way to do it in existing excel functions. It would
seem fundemental to me to be able to find out the name of, say, sheet 42
in my program.
Click to expand...

Perhaps you should have mentioned this in your original post. Perhaps you
should have posted in .programming, instead of here, in .newusers (if it's a
vba question, you're hardly a "new user").

Hang around awhile for possible insights from other responders versed in
vba. If none drops by, suggest you put in a fresh post in .programming.

---
 
Here's a little UDF to do it

Public Function Findit(idx As Long)
Findit = Application.Caller.Parent.Parent.Sheets(idx).Name
End Function


if you want to call it from VBA, use this

Public Function Findit(idx As Long, Optional wb)
If IsMissing(wb) Then
Set wb = ThisWorkbook
End If
Findit = wb.Sheets(idx).Name
End Function


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Back
Top