Getting name of worksheet

[Jack Schitt]

=RIGHT(CELL("filename",$A$5),1+LEN(CELL("filename",$A$5))-FIND("[",CELL("fil
ename",$A$5)))

returns "#VALUE!"

when the worksheet name is identical to the workbook name.

Any way around that, so that it returns the worksheet name both when it is
identical and not identical to workbook name?

 

[Norman Harker]

Hi Jack!

Try:
=RIGHT(CELL("filename",A2),LEN(CELL("filename",A2))-FIND("]",CELL("filename",A2),1))
Returns the sheet name as long as the file has been saved

Your formula is returning (eg)

[Delete1 OK.xls]Delete1 OK

 

[Jack Schitt]

Hi Jack!

Try:
=RIGHT(CELL("filename",A2),LEN(CELL("filename",A2))-FIND("]",CELL("filename"
,A2),1))
Returns the sheet name as long as the file has been saved

Your formula is returning (eg)

[Delete1 OK.xls]Delete1 OK

I wish it did. Returning the above is what I desire. But it returns
instead #VALUE!
Furthermore, your solution also returns #VALUE! on my computer.
If I change the name of the worksheet so that it does not match the name of
the workbook then both your solution and my solution work, except that your
solution omits the name of the workbook in the returned value, which may be
useful in other circumstances.

 

[Peo Sjoblom]

If you want the sheet name you can use

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

if you want both workbook and sheet name

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1)),255)

just tested on a workbook with the same name as the sheet

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

Hi Jack!

Try:

=RIGHT(CELL("filename",A2),LEN(CELL("filename",A2))-FIND("]",CELL("filename"

,A2),1))
Returns the sheet name as long as the file has been saved

Your formula is returning (eg)

[Delete1 OK.xls]Delete1 OK

I wish it did. Returning the above is what I desire. But it returns
instead #VALUE!
Furthermore, your solution also returns #VALUE! on my computer.
If I change the name of the worksheet so that it does not match the name of
the workbook then both your solution and my solution work, except that your
solution omits the name of the workbook in the returned value, which may be
useful in other circumstances.

 

[Jack Schitt]

This is very strange, because Peo's solution creates the same problems on my
machine as all of the other suggested solutions. I tried rebooting and
putting the solution in a fresh workbook and I keep having the same problem.
My problem appears to be with the behaviour of the CELL("filename",cell_ref)
function. All suggested solutions use this as the base, and so I tried the
effect of entering in a cell simply the following:
=CELL("filename",A1)
Then I saved the file as
C:\mypath\test.xls
To be safe I then closed and reoponed the workbook. So far the only
worksheet is "Sheet1".
The value returned in the cell is, as predicted:
"C:\mypath\[test.xls]Sheet1"
I now change the name of the worksheet from "Sheet1" to "test"
and the value returned in the cell changes to:
"C:\mypath\test.xls"
The cause of the #VALUE! error in the more complex formulae now becomes
obvious.
Can no-one else reproduce this anomolous result returned by CELL()?
I am using Office XP professional, Service Pack 3
If I click on Help/About, it reports the following version:
MicroSoft Excel 2002 (10.6501.6626) SP3
If I go to the MicroSoft office update site and click on scan for updates it
reports that everything is up to date.

--
Return email address is not as DEEP as it appears

If you want the sheet name you can use

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

if you want both workbook and sheet name

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1)),255)

just tested on a workbook with the same name as the sheet

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

=RIGHT(CELL("filename",A2),LEN(CELL("filename",A2))-FIND("]",CELL("filename"

,A2),1))
Returns the sheet name as long as the file has been saved

Your formula is returning (eg)

[Delete1 OK.xls]Delete1 OK

I wish it did. Returning the above is what I desire. But it returns
instead #VALUE!
Furthermore, your solution also returns #VALUE! on my computer.
If I change the name of the worksheet so that it does not match the name of
the workbook then both your solution and my solution work, except that your
solution omits the name of the workbook in the returned value, which may be
useful in other circumstances.

 

[Norman Harker]

Hi Jack!

I tested mine and yours was not getting the #VALUE error with or
without the sheet and filename being the same.

I think the names equality is an irrelevance. It must be something
else.

Peo's suggestions are more efficient and it will be interesting to see
if you get the same errors.

 

[Jack Schitt]

Thanks for helping Norman. I have responded to Peo's post.

 

[Jack Schitt]

A bit more info:
The problem only arises when the specified worksheet is the only worksheet
in the workbook, as well as bearing the same name as the workbook (excluding
the .xls extension)

--
Return email address is not as DEEP as it appears

This is very strange, because Peo's solution creates the same problems on my
machine as all of the other suggested solutions. I tried rebooting and
putting the solution in a fresh workbook and I keep having the same problem.
My problem appears to be with the behaviour of the CELL("filename",cell_ref)
function. All suggested solutions use this as the base, and so I tried the
effect of entering in a cell simply the following:
=CELL("filename",A1)
Then I saved the file as
C:\mypath\test.xls
To be safe I then closed and reoponed the workbook. So far the only
worksheet is "Sheet1".
The value returned in the cell is, as predicted:
"C:\mypath\[test.xls]Sheet1"
I now change the name of the worksheet from "Sheet1" to "test"
and the value returned in the cell changes to:
"C:\mypath\test.xls"
The cause of the #VALUE! error in the more complex formulae now becomes
obvious.
Can no-one else reproduce this anomolous result returned by CELL()?
I am using Office XP professional, Service Pack 3
If I click on Help/About, it reports the following version:
MicroSoft Excel 2002 (10.6501.6626) SP3
If I go to the MicroSoft office update site and click on scan for updates it
reports that everything is up to date.

--
Return email address is not as DEEP as it appears

If you want the sheet name you can use

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

if you want both workbook and sheet name

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1)),255)

just tested on a workbook with the same name as the sheet

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

Hi Jack!

Try:

=RIGHT(CELL("filename",A2),LEN(CELL("filename",A2))-FIND("]",CELL("filename"

,A2),1))
Returns the sheet name as long as the file has been saved

Your formula is returning (eg)

[Delete1 OK.xls]Delete1 OK

I wish it did. Returning the above is what I desire. But it returns
instead #VALUE!
Furthermore, your solution also returns #VALUE! on my computer.
If I change the name of the worksheet so that it does not match the

name
of

the workbook then both your solution and my solution work, except that your
solution omits the name of the workbook in the returned value, which

may
be

useful in other circumstances.

 

[Anders S]

Jack,

Just so you know that you're not alone, I get the same result on my
Office XP with =CELL("filename",A1) and one worksheet only.
Why that is, I don't know though.

Regards
Anders Silven

 

[Peo Sjoblom]

Ok, I can reproduce that. Excel 2003 behaves the
same way.

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

A bit more info:
The problem only arises when the specified worksheet is the only worksheet
in the workbook, as well as bearing the same name as the workbook (excluding
the .xls extension)

--
Return email address is not as DEEP as it appears

This is very strange, because Peo's solution creates the same problems

on

my
machine as all of the other suggested solutions. I tried rebooting and
putting the solution in a fresh workbook and I keep having the same problem.
My problem appears to be with the behaviour of the CELL("filename",cell_ref)
function. All suggested solutions use this as the base, and so I tried the
effect of entering in a cell simply the following:
=CELL("filename",A1)
Then I saved the file as
C:\mypath\test.xls
To be safe I then closed and reoponed the workbook. So far the only
worksheet is "Sheet1".
The value returned in the cell is, as predicted:
"C:\mypath\[test.xls]Sheet1"
I now change the name of the worksheet from "Sheet1" to "test"
and the value returned in the cell changes to:
"C:\mypath\test.xls"
The cause of the #VALUE! error in the more complex formulae now becomes
obvious.
Can no-one else reproduce this anomolous result returned by CELL()?
I am using Office XP professional, Service Pack 3
If I click on Help/About, it reports the following version:
MicroSoft Excel 2002 (10.6501.6626) SP3
If I go to the MicroSoft office update site and click on scan for

updates

it
reports that everything is up to date.

--
Return email address is not as DEEP as it appears

If you want the sheet name you can use

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

if you want both workbook and sheet name

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1)),255)

just tested on a workbook with the same name as the sheet

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

Hi Jack!

Try:

=RIGHT(CELL("filename",A2),LEN(CELL("filename",A2))-FIND("]",CELL("filename"

,A2),1))
Returns the sheet name as long as the file has been saved

Your formula is returning (eg)

[Delete1 OK.xls]Delete1 OK

I wish it did. Returning the above is what I desire. But it returns
instead #VALUE!
Furthermore, your solution also returns #VALUE! on my computer.
If I change the name of the worksheet so that it does not match the name
of
the workbook then both your solution and my solution work, except that
your
solution omits the name of the workbook in the returned value, which may
be
useful in other circumstances.

 

[Norman Harker]

Hi Jack!

Here's a formula for finding Sheet name that seem to cover the
eventuality of sheet name = file name where there is only one sheet in
the workbook:

Sheet name:
=IF(LEN(CELL("filename",A1))=LEN(SUBSTITUTE(CELL("filename",A1),"[","")),MID(SUBSTITUTE(CELL("filename",A1),"\","*",LEN(CELL("filename",A1))-LEN(SUBSTITUTE(CELL("filename",A1),"\",""))),FIND("*",SUBSTITUTE(CELL("filename",A1),"\","*",LEN(CELL("filename",A1))-LEN(SUBSTITUTE(CELL("filename",A1),"\",""))))+1,FIND(".",SUBSTITUTE(CELL("filename",A1),"\","*",LEN(CELL("filename",A1))-LEN(SUBSTITUTE(CELL("filename",A1),"\",""))))-FIND("*",SUBSTITUTE(CELL("filename",A1),"\","*",LEN(CELL("filename",A1))-LEN(SUBSTITUTE(CELL("filename",A1),"\",""))))-1),MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32))

To return the file name only and cover the same eventualities use:

File name:
=IF(LEN(CELL("filename",A1))=LEN(SUBSTITUTE(CELL("filename",A1),"[","")),MID(SUBSTITUTE(CELL("filename",A1),"\","*",LEN(CELL("filename",A1))-LEN(SUBSTITUTE(CELL("filename",A1),"\",""))),FIND("*",SUBSTITUTE(CELL("filename",A1),"\","*",LEN(CELL("filename",A1))-LEN(SUBSTITUTE(CELL("filename",A1),"\",""))))+1,32),MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CELL("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1))

Both seem to test OK but I'll not speak for efficiency Certainly
using intermediate formulas would make things a bit prettier.

 

[Jack Schitt]

Thanks. You worked hard on that, and I will keep the solutions for future
reference.
However, know that I know that the problem is isolated to a particular
situation I am happy to leave the bug in the code knowing that I can avoid
the situation that causes the problem,

 

[David McRitchie]

Hi Jack,
One thing, I think, you weren't told and possibly may be why things
didn't work at first for you. You must have saved the workbook
before you can get the pathname and extractions from pathname.
http://www.mvps.org/dmcritchie/excel/pathname.htm

Thanks. You worked hard on that, and I will keep the solutions for future
reference.
However, know that I know that the problem is isolated to a particular
situation I am happy to leave the bug in the code knowing that I can avoid
the situation that causes the problem,

--
Return email address is not as DEEP as it appears

Hi Jack!

Here's a formula for finding Sheet name that seem to cover the
eventuality of sheet name = file name where there is only one sheet in
the workbook:

Sheet name:
=IF(LEN(CELL("filename",A1))=LEN(SUBSTITUTE(CELL("filename",A1),"[","")),MID
(SUBSTITUTE(CELL("filename",A1),"\","*",LEN(CELL("filename",A1))-LEN(SUBSTIT
UTE(CELL("filename",A1),"\",""))),FIND("*",SUBSTITUTE(CELL("filename",A1),"\
","*",LEN(CELL("filename",A1))-LEN(SUBSTITUTE(CELL("filename",A1),"\",""))))
+1,FIND(".",SUBSTITUTE(CELL("filename",A1),"\","*",LEN(CELL("filename",A1))-
LEN(SUBSTITUTE(CELL("filename",A1),"\",""))))-FIND("*",SUBSTITUTE(CELL("file
name",A1),"\","*",LEN(CELL("filename",A1))-LEN(SUBSTITUTE(CELL("filename",A1
),"\",""))))-1),MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32))

To return the file name only and cover the same eventualities use:

File name:
=IF(LEN(CELL("filename",A1))=LEN(SUBSTITUTE(CELL("filename",A1),"[","")),MID
(SUBSTITUTE(CELL("filename",A1),"\","*",LEN(CELL("filename",A1))-LEN(SUBSTIT
UTE(CELL("filename",A1),"\",""))),FIND("*",SUBSTITUTE(CELL("filename",A1),"\
","*",LEN(CELL("filename",A1))-LEN(SUBSTITUTE(CELL("filename",A1),"\",""))))
+1,32),MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CE
LL("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1))

Both seem to test OK but I'll not speak for efficiency Certainly
using intermediate formulas would make things a bit prettier.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)

 

[Norman Harker]

Hi David!

Look a little closer. We have a special case here where the usual
parsing of CELL doesn't work because of an unusual case of what it
returns where file name and sheet name are the same and there is only
one sheet in the file.

 

[David McRitchie]

Hi Norman,
Okay, I see that, now. How in the world did you come
up with that LONG formula, what is it's design, and how
did you manage that without making mistakes. Did
you construct it on a worksheet.

Are you guys running on Macs, or is it that XP allows you
to create worksheets with characters previously prohibited
for worksheet names such as a backward slash, or a colon.

In Excel 2000 this is what I would get, if I tried to rename
worksheet:
-------------------------
NAME = While renaming a sheet or chart, you entered an
invalid name. Try one of the following:

• Make sure the name you entered does not exceed 31 characters.
• Make sure the name does not contain any of the
following characters: : \ / ? * [ or ]
• Make sure you did not leave the name blank

 

[Norman Harker]

Hi David!

Basic construction technique was to use the John Walkenbach termed
megaformula approach.

The answer is built up in a series of cells. When you have the answer
in a cell you then substitute references to earlier cells in the
process by the formulas that are contained in those cells. It looks
more complex than it is and as a general rule I would discourage the
approach and favour building the formula in a series of cells that you
might then hide. When you come back to megaformulas, they can be
extremely difficult to understand and amend and even more difficult
for someone else to understand and amend. In any event, a UDF would
generally be preferred.

The approach noted that with a single sheet workbook with the sheet
name the same as the workbook name the standard:

A4:
=CELL("filename",A1)
returned (eg)
C:\My Files\NewsGroups\Delete2 OK.xls

This now has to be parsed to strip out the file name.

A6:
=LEN(A4)-LEN(SUBSTITUTE(A4,"\",""))
Returns the fact that there are three \ in the file path

A7:
=SUBSTITUTE(A4,"\","*",A6)
Returns:
C:\My Files\NewsGroups*Delete2 OK.xls

I can now parse out the file name:
A9:
=MID(A7,FIND("*",A7)+1,32)
Returns:
Delete2 OK.xls

I can now substitute the formula in A7 for the references to A7 and
this gives me:

=MID(SUBSTITUTE(A4,"\","*",A6),FIND("*",SUBSTITUTE(A4,"\","*",A6))+1,32)

I now substitute the formulas in A4 and A6 for the references to those
cells and that gives me:

=MID(SUBSTITUTE(CELL("filename",A1),"\","*",LEN(A4)-LEN(SUBSTITUTE(A4,"\",""))),FIND("*",SUBSTITUTE(CELL("filename",A1),"\","*",LEN(A4)-LEN(SUBSTITUTE(A4,"\",""))))+1,32)

The substitutions of A6 still leave references to A4 which need
substituting by the formula in A4 and that gives me:

=MID(SUBSTITUTE(CELL("filename",A1),"\","*",LEN(CELL("filename",A1))-LEN(SUBSTITUTE(CELL("filename",A1),"\",""))),FIND("*",SUBSTITUTE(CELL("filename",A1),"\","*",LEN(CELL("filename",A1))-LEN(SUBSTITUTE(CELL("filename",A1),"\",""))))+1,32)

Now this works OK until I add a sheet to the file. In that (normal)
case I get a return of:
[Delete2 OK.xls]Delete2 OK

For the normal cases I can use the standard formula:

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CELL("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1)
Returns: Delete2 OK.xls

So I have a normal case and an abnormal case formula. I use an IF
function to select between them and I choose to test for abnormality
by seeing if the return of the CELL formula contains a [. If the
length of the return without [ is the same as the length with any [,
then the abnormal case applies. Otherwise it is normal case.

That gives me the final version for getting the filename:
=IF(LEN(CELL("filename",A1))=LEN(SUBSTITUTE(CELL("filename",A1),"[","")),MID(SUBSTITUTE(CELL("filename",A1),"\","*",LEN(CELL("filename",A1))-LEN(SUBSTITUTE(CELL("filename",A1),"\",""))),FIND("*",SUBSTITUTE(CELL("filename",A1),"\","*",LEN(CELL("filename",A1))-LEN(SUBSTITUTE(CELL("filename",A1),"\",""))))+1,32),MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CELL("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1))

Returns:
Delete2 OK.xls

Checks OK for both normal cases with more than one sheet or where the
name of the sheet is different from the name of the workbook. Checks
OK for the special case where there's only one sheet and it has the
same name as the workbook.

I could use an IF ISERROR approach to make the selection but rejected
that because there could be other sources of error such as a non-saved
file.

The sheet formula uses the same construction approach. I could just
strip off the extension from the file name in abnormal cases (note the
names are the same). However, I can do this more directly because when
I have:

C:\My Files\NewsGroups*Delete2 OK.xls
The sheet name the same as the file name (without the extension xls)
and I can extract that using MID and a calculation of the number of
characters needed from after * and before the .

That gave me the "abnormal" formula:

=MID(SUBSTITUTE(CELL("filename",A1),"\","*",LEN(CELL("filename",A1))-LEN(SUBSTITUTE(CELL("filename",A1),"\",""))),FIND("*",SUBSTITUTE(CELL("filename",A1),"\","*",LEN(CELL("filename",A1))-LEN(SUBSTITUTE(CELL("filename",A1),"\",""))))+1,FIND(".",SUBSTITUTE(CELL("filename",A1),"\","*",LEN(CELL("filename",A1))-LEN(SUBSTITUTE(CELL("filename",A1),"\",""))))-FIND("*",SUBSTITUTE(CELL("filename",A1),"\","*",LEN(CELL("filename",A1))-LEN(SUBSTITUTE(CELL("filename",A1),"\",""))))-1)

The "normal" formula is:

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

So using the same IF function selection criteria as before:

That gives me a method of getting the sheet name in both normal and
abnormal cases:
=IF(LEN(CELL("filename",A1))=LEN(SUBSTITUTE(CELL("filename",A1),"[","")),MID(SUBSTITUTE(CELL("filename",A1),"\","*",LEN(CELL("filename",A1))-LEN(SUBSTITUTE(CELL("filename",A1),"\",""))),FIND("*",SUBSTITUTE(CELL("filename",A1),"\","*",LEN(CELL("filename",A1))-LEN(SUBSTITUTE(CELL("filename",A1),"\",""))))+1,FIND(".",SUBSTITUTE(CELL("filename",A1),"\","*",LEN(CELL("filename",A1))-LEN(SUBSTITUTE(CELL("filename",A1),"\",""))))-FIND("*",SUBSTITUTE(CELL("filename",A1),"\","*",LEN(CELL("filename",A1))-LEN(SUBSTITUTE(CELL("filename",A1),"\",""))))-1),MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32))


Like I said, I'm sure that these could be made more efficient. I
wouldn't recommend the approach over building it up in steps in
separate cells.

And in any event it would be much better to use the User Defined
Function:

Function SHEETNAME() As String
'Volatile covers re-naming but can impact on performance.
Application.Volatile True
SHEETNAME = [Cell("FileName",A1]
SHEETNAME = Application.ActiveSheet.Name
End Function

This UDF does not suffer from the problem that Jack faced when he hit
a file with only one sheet with the same name as the file.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)

Hi Norman,
Okay, I see that, now. How in the world did you come
up with that LONG formula, what is it's design, and how
did you manage that without making mistakes. Did
you construct it on a worksheet.

Are you guys running on Macs, or is it that XP allows you
to create worksheets with characters previously prohibited
for worksheet names such as a backward slash, or a colon.

In Excel 2000 this is what I would get, if I tried to rename
worksheet:
-------------------------
NAME = While renaming a sheet or chart, you entered an
invalid name. Try one of the following:

• Make sure the name you entered does not exceed 31 characters.
• Make sure the name does not contain any of the
following characters: : \ / ? * [ or ]
• Make sure you did not leave the name blank
--------------------------
--David McRitchie, Microsoft MVP - Excel

Hi David!

Look a little closer. We have a special case here where the usual
parsing of CELL doesn't work because of an unusual case of what it
returns where file name and sheet name are the same and there is
only
one sheet in the file.

 

[David McRitchie]

I guess this is just Excel XP where
only sheetname and it being named: delete2 OK.xls
same as workbook name: delete2 OK.xls
in the full dsname of c:\my files\news groups\delete2 OK.xls
using =CELL("filename",A1)
gives: C:\my files\news groups\delete2 OK.xls
instead of what you get in Excel 2000:
C:\my files\news groups\[delete2 OK.xls]delete2 OK.xls

and using =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
in Excel XP give: #VALUE! error because no square bracket
in Excel 2000 gives expected: delete2 OK.xls

 

[Norman Harker]

Hi David!

The problem appears to exist in Excel 2000 as well as with Excel 2003.
But it may be a Windows XP thing; I've not checked that out and it
could well be the problem!

Here's the problem which I've used Excel 2000 on as a check of same in
Excel 2003:

Sheet name I used was

Delete2 OK

Workbook name is:

Delete2 OK.xls

There is only the one sheet in the workbook.

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32)
Returns: #VALUE!

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1)),255)
Returns: #VALUE!

=CELL("filename",A1)
Returns: C:\My Files\NewsGroups\Delete2 OK.xls


If I insert another sheet (and recalculate), those three formulas
return the "normal"

Delete2 OK
[Delete2 OK.xls]Delete2 OK
C:\My Files\NewsGroups\[Delete2 OK.xls]Delete2 OK

So, using a formula approach, I have to parse two alternatives if I
want to cover the unlikely event where this problem comes up.

 

[David McRitchie]

I'll go along with blaming it on Windows XP for now, since I have
Windows 2000 Version 5.0 (build 2195: Service Pack4)
Excel 2000 (09.0.6026 SP-3)
and don't have the problem.

I tested pathname in sheet3 before deleting sheets 2 & 3
otherwise exactly same as your tests, except for the results.

 

[JE McGimpsey]

Wouldn't concede yet, David.

Get the same problem with MacXL04 when I enter

=CELL("filename",A1)

in a worksheet with the same name as the (single-sheet) workbook. It
returns the path and worksheet name, but no workbook name in brackets.

So it's unlikely to be Windows XP (unless it was carried over from
importing something from MacOS into Windows XP filehandling, which I
suppose is possible).