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" wrote:
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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

John


Max wrote:
"John" wrote:

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

'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


On Wed, 26 Jul 2006 13:37:57 +0100, John wrote:

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

John


Max wrote:
"John" wrote:

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

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

SteveW wrote:

'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


On Wed, 26 Jul 2006 13:37:57 +0100, John wrote:

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

John


Max wrote:

"John" wrote:

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

"John" wrote:
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.

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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)

"Max" wrote in message
...
"John" wrote:
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.

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---