Norman Harker said: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.
Daniel Bonallack said: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 said: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)
Norman Harker said: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.
Daniel Bonallack said: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 said: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.
Bob Phillips said: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)
Norman Harker said: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?