returning the worksheet name with a formula

[Daniel Bonallack]

Is this possible? If my sheet is called "Daniel", can I
return that value to a cell without making a custom VBA
function?

 

[Norman Harker]

Hi Daniel!

To get the Worksheet name we parse the =CELL("filename",A1) formula.
Noting that the sheet name is between the parentheses "[" and "]" we
can use the following:



=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("fil
ename",A1))-FIND("]",CELL("filename",A1)))


But a shorter version relies on the knowledge that the sheet name is
at the end of the string returned by the =CELL("filename",A1) formula:

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



The final number relies on the knowledge that the worksheet name
cannot exceed 31 characters; I suppose that we use 255 just in case
Microsoft decide in a later version to increase this (unspecified)
specification.


Both formulas will return the dreaded #VALUE! if the workbook hasn't
been saved yet.



--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Bob Phillips]

Daniel,

Here's a neat little trick that I haven't seen before based upon this
technique.

I like to have workbook names for common functions like this. The problem is
if you add a name, such as 'sh.name' with the formula as provided by Norman
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("filename"
,A1))-FIND("]",CELL("filename",A1)))
the formula is anchored to the sheet that was active when it is defined. So
if it is defined on Sheet1, and you put '=sh.name' in a Sheet1 cell you will
get Sheet1. All OK so far. But if you put =sh.name on Sheet2, you still get
Sheet1.

To circumvent this, change the formula slightly, to
=MID(CELL("filename",!$A$1),FIND("]",CELL("filename",!$A$1))+1,255)
What I have done here is add a sheet delimiter (!) before the cell. You
can't use the sheet name as that will anchor it as befor, but you can just
add delimiter and it works. Now =sh.name on Sheet1 returns Sheet1, and
Sheet2 on Sheet2.

I like it<g>.

By the way, the formula doesn't work if put in a worksheet function, only as
a workbook name.

--

HTH

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

Hi Daniel!

To get the Worksheet name we parse the =CELL("filename",A1) formula.
Noting that the sheet name is between the parentheses "[" and "]" we
can use the following:



=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("fil
ename",A1))-FIND("]",CELL("filename",A1)))


But a shorter version relies on the knowledge that the sheet name is
at the end of the string returned by the =CELL("filename",A1) formula:

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



The final number relies on the knowledge that the worksheet name
cannot exceed 31 characters; I suppose that we use 255 just in case
Microsoft decide in a later version to increase this (unspecified)
specification.


Both formulas will return the dreaded #VALUE! if the workbook hasn't
been saved yet.



--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Is this possible? If my sheet is called "Daniel", can I
return that value to a cell without making a custom VBA
function?

 

[Norman Harker]

Hi Bob!

That I like! The Bob Phillips sheet name approach. Even though I'm not
generally very keen on defined formulas.

I tend to use a custom function but defined names have advantages.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Daniel,

Here's a neat little trick that I haven't seen before based upon this
technique.

I like to have workbook names for common functions like this. The problem is
if you add a name, such as 'sh.name' with the formula as provided by Norman=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("fil
ename"
,A1))-FIND("]",CELL("filename",A1)))
the formula is anchored to the sheet that was active when it is defined. So
if it is defined on Sheet1, and you put '=sh.name' in a Sheet1 cell you will
get Sheet1. All OK so far. But if you put =sh.name on Sheet2, you still get
Sheet1.

To circumvent this, change the formula slightly, to
=MID(CELL("filename",!$A$1),FIND("]",CELL("filename",!$A$1))+1,255)
What I have done here is add a sheet delimiter (!) before the cell. You
can't use the sheet name as that will anchor it as befor, but you can just
add delimiter and it works. Now =sh.name on Sheet1 returns Sheet1, and
Sheet2 on Sheet2.

I like it<g>.

By the way, the formula doesn't work if put in a worksheet function, only as
a workbook name.

--

HTH

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

Hi Daniel!

To get the Worksheet name we parse the =CELL("filename",A1) formula.
Noting that the sheet name is between the parentheses "[" and "]" we
can use the following:



=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("fil
ename",A1))-FIND("]",CELL("filename",A1)))


But a shorter version relies on the knowledge that the sheet name is
at the end of the string returned by the =CELL("filename",A1) formula:

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



The final number relies on the knowledge that the worksheet name
cannot exceed 31 characters; I suppose that we use 255 just in case
Microsoft decide in a later version to increase this (unspecified)
specification.


Both formulas will return the dreaded #VALUE! if the workbook hasn't
been saved yet.



--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Is this possible? If my sheet is called "Daniel", can I
return that value to a cell without making a custom VBA
function?

 

[Bob Phillips]

Thank you Norm, and it's good to see you again.

World Cup was good wasn't it<VBG>

Bob

Hi Bob!

That I like! The Bob Phillips sheet name approach. Even though I'm not
generally very keen on defined formulas.

I tend to use a custom function but defined names have advantages.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Daniel,

Here's a neat little trick that I haven't seen before based upon this
technique.

I like to have workbook names for common functions like this. The problem is
if you add a name, such as 'sh.name' with the formula as provided by Norman=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("fil
ename"
,A1))-FIND("]",CELL("filename",A1)))
the formula is anchored to the sheet that was active when it is defined. So
if it is defined on Sheet1, and you put '=sh.name' in a Sheet1 cell you will
get Sheet1. All OK so far. But if you put =sh.name on Sheet2, you still get
Sheet1.

To circumvent this, change the formula slightly, to
=MID(CELL("filename",!$A$1),FIND("]",CELL("filename",!$A$1))+1,255)
What I have done here is add a sheet delimiter (!) before the cell. You
can't use the sheet name as that will anchor it as befor, but you can just
add delimiter and it works. Now =sh.name on Sheet1 returns Sheet1, and
Sheet2 on Sheet2.

I like it<g>.

By the way, the formula doesn't work if put in a worksheet function, only as
a workbook name.

--

HTH

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

Hi Daniel!

To get the Worksheet name we parse the =CELL("filename",A1) formula.
Noting that the sheet name is between the parentheses "[" and "]" we
can use the following:



=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("fil
ename",A1))-FIND("]",CELL("filename",A1)))


But a shorter version relies on the knowledge that the sheet name is
at the end of the string returned by the =CELL("filename",A1) formula:

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



The final number relies on the knowledge that the worksheet name
cannot exceed 31 characters; I suppose that we use 255 just in case
Microsoft decide in a later version to increase this (unspecified)
specification.


Both formulas will return the dreaded #VALUE! if the workbook hasn't
been saved yet.



--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Is this possible? If my sheet is called "Daniel", can I
return that value to a cell without making a custom VBA
function?

 

[Norman Harker]

Hi Bob!

"World Cup was good wasn't it<VBG>"

HO:HO (The Santa Claus column). But another range sprang to mind!


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.