Returning Worksheet Name

S

sandbag

Another simple question...

How do you reference the name of the current worksheet?

For example:

if the current worksheet is called "Week1", I would like to set A1=1

...

if the current worksheet is called "Week52", I would like to set A1=52
 
B

Bob Phillips

Here are a few file alternatives to a cell, including sheet name

File path and file name:
=CELL("filename",A1)

File path only
=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)

File name only
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CELL("fi
lename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

The sheet name
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename"
,A1),1))


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
N

Norman Harker

Hi Sandbag!

One way:

=RIGHT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32),LEN(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32))-4)

It's based upon a parsing of the formula for finding the sheet name:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32)

The workbook must have been saved for the formulas to work.
 
H

Harlan Grove

Bob Phillips said:
File path and file name:
=CELL("filename",A1)
Click to expand...

Quibble: this is path, file *AND* worksheet name.
The sheet name
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",
CELL("filename",A1),1))
Click to expand...
....

There's no need to waste cycles on LEN or a 3rd CELL call.

=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,32)
 
B

Bob Phillips

Harlan Grove said:
Quibble: this is path, file *AND* worksheet name.
Click to expand...

Indeed it is. I will update my reference.
The sheet name
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",
CELL("filename",A1),1))
Click to expand...
...

There's no need to waste cycles on LEN or a 3rd CELL call.

=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,32)
Click to expand...

I think we have been here before. I'll change just to avoid a third time<G>
 
Top