Excel CELL bug

  • Thread starter Thread starter ArtHendrickson
  • Start date Start date
A

ArtHendrickson

Back in Apr 13 2004, 10basetom posted about what he believed to be
flaw with the Excel function CELL. The replies corrected that belief.
believe that I have found a bug with CELL. I'm using Excel 200
(9.0.6926 SP-3).

Create a one-worksheet workbook. Name the worksheet George and save th
workbook as George--the same name as the one worksheet. Now enter > =CELL("filename",A1) into any cell. My results are not as expected. The workbook name is no
surrounded by brackets and the worksheet name is missing.

Either, change the name of the workbook, the worksheet, or add anothe
worksheet and then the results are as expected.

Does this error exist in later versions of Excel?

Art
 
...
....
Create a one-worksheet workbook. Name the worksheet George and save the
workbook as George--the same name as the one worksheet. Now enter
=CELL("filename",A1) into any cell. My results are not as expected. The
workbook name is not surrounded by brackets and the worksheet name is
missing.
Click to expand...
....

This is *not* a bug, though it's inadequately documented (as is so much
else). This is for backward compatibility with Excel 4 and prior in which
..XLS files could only contain a single worksheet. [Excel 4 provided .XLW
workbooks which could contain several virtual .XLS files.] If you save a
single worksheet file in XL2, XL3 or XL4 .XLS formats, the single worksheet
in such files will have the same name as the as the workbook's base filename
(i.e., without the .XLS extension). The syntax for external references in
those old versions was 'filename.xls'!Address, and that's what all versions
of Excel up to Excel 2003 (Windows) and Excel 2004 (Mac) will use when
referring to such workbooks. Further, workbook-level names are also given in
the same syntax, e.g., 'filename.xls'!DefinedName.

This is Excel's (perverse) external reference syntax. There's nothing to
'fix' (Microsoft has a demonstrated tendency not to change poor syntax).

On a tangent: Excel's external referencing syntax is VERY BADLY DESIGNED.
Square brackets and parentheses are legal characters in Windows and MacOS
filenames, so while Excel may object to saving files with these characters
in their filenames, nothing prevents users from renaming them with such
characters in the OS.

The problem this raises, which *IS* a bug in Excel (or a more thoroughly
stupid design decision than the external reference syntax), is that Excel
will open files with these characters in the base filename. Excel then mungs
the filenames in memory.

Save an .XLS file. Rename it [foo].xls and make a copy of it named
(foo).xls. Open (foo).xls first. Excel will show it's name as (foo).xls. Now
open [foo].xls, and Excel will show this second file's name as (foo).xls as
well. So Excel can be fooled into having two files open in memory at the
same time with what it believes are the same filenames. If you then use
Point mode to refer to a cell in [foo].xls, Excel will happily return a
reference to the corresponding range in (foo).xls.

Microsoft *should* reprogram Excel so that it won't open any file if it
contains characters in the base filename that Excel won't permit when saving
the file. Then it should eliminate the code that converts square brackets in
base filenames into parentheses. However, Microsoft is impervious to matters
of 'should' and 'ought', so this is unlikely ever to be fixed.
 
Back
Top