=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,31)
The CELL bit is as follows and simply returns the full filepath of the sheet name.
=cell(info_type,reference) is a function in Excel that allows you to specify various arguments
Syntax
CELL(info_type,reference)
Info_type is a text value that specifies what type of cell information you want. The following
list shows the possible values of info_type and the corresponding results.
Info_type Returns
"address" Reference of the first cell in reference, as text.
"col" Column number of the cell in reference.
"color" 1 if the cell is formatted in color for negative values; otherwise returns 0
(zero).
"contents" Value of the upper-left cell in reference; not a formula.
"filename" Filename (including full path) of the file that contains reference, as text.
Returns empty text ("") if the worksheet that contains reference has not yet been saved.
<snip> See help for the rest of the arguments
The CELL part of the formula will return something like the following:-
C:\Documents and Settings\Default\My Documents\[Test1.xls]Sheet1
The MID bit of the formula simply looks for the part of the string that is a ' ]' which is the bit
just before the sheet name, and then gives you everything after it. The 31 specifies how many
characters to return if they exist after the ], and seeing as that is the max number of characters
you can have in a sheet name, you know it will give you the whole name, and the +1 simply says
start at the first character past the ].
--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP
-------------------------------------------------------------------------- --
There's no 'I' in 'Team'
-------------------------------------------------------------------------- --
Nick Fuller @ BFA said:
Thanks a lot,
I don't understand the arguments because I am a very basic user with enough
experience to be barely dangerous, but it worked perfectly, I appreciate it.
Ken Wright said:
As long as the workbook has been saved, the following will return the sheetname:-
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,31)
--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP
----------------------------------------------------------------------------------------------------------------------------------------------------
--
Is there a way to retrieve the name of the sheet within a cell of
the
same
sheet. For instance I have a timesheet that I input my daily
billable
time
into for our accounting department. It is a workbook with multiple sheets
named by the day the period closing (i.e July 9, 2003). I would like the
name of the sheet to automatically be returned in a date column on the
spreadsheet somewhere. I thought there would be a function like
='sheetname'
any help would be greatly appreciated.
