Hi IamRonin!
You need the following base formula:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
There's no reason why you can't use a cell reference to a cell in another
sheet in place of the "traditional" A1.
But some explanation might help you understand the use of three functions
and some of the logic that isn't that obvious if you don't have it pointed
out to you.
=CELL("filename")
Returns the full drive, folder, file name and sheetname of a workbook
provided that it has been saved.
However, the CELL function is volatile which means that it recalculates
every time the workbook gets recalculated. This means that in the form given
it will return the worksheet name of whatever the active sheet is at the
time of recalculation. So if you put this in Sheet1 and then go to Sheet2
and do some calculation, when you come back to Sheet1 the reference will be
to Sheet2. So we modify the formula by putting a reference to any cell in
the sheet we want the name for.
=CELL("filename",A1)
A sample return might be:
C:\My Files\NewsGroups\Posting Testers\2003-02\[2003-02-08 Sheet
Name.xls]Sheet1
All we want is the worksheet name at the end.
MID is a text 'parsing' function that has the syntax:
=MID(text,start_num,num_chars)
In this case
CELL("filename",A1) provides us with the text that we are 'parsing'.
We use:
FIND("]",CELL("filename",A1))+1
To find the position of the first cell after the ] which is always where the
worksheet name's first character will be located. FIND has syntax:
=FIND(find_text,within_text,start_num)
find_text is "]", CELL("filename", A1) gives us the within_text and
start_num is optional and not needed if we want to search the entire
'string' of text. Obvious the position of the first character of the
worksheet name is 1 more than the position number of the ] that encloses the
workbook's name.
Finally we throw in 255 as the number of characters. I don't quite know why
we select 255 rather than 31 which is the maximum length of a worksheet
name, but I suppose it's because we always have done.
But a good defined formula approach may be to define a formula as:
=MID(CELL("filename",!$A$1),FIND("]",CELL("filename",!$A$1))+1,255)
If you define sh.name as that formula you can then use =sh.name to get the
sheet that the cell is in.
--
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
