Excel CELL bug

  • Thread starter Thread starter 10basetom
  • Start date Start date
1

10basetom

Hi, I think I discovered an Excel bug with CELL("filename"). T
reproduce:

1. Create two new Excel files -- you can name them anything.
2. Enter CELL("filename") into each spreadsheet.
3. Notice that as soon as you enter this formula into the secon
spreadsheet, the value in the first spreadsheet also changes to th
same filename as the second spreadsheet!

Does anybody have a workaround for this? I currently rely on thi
function to extract a date from the filename to use in my report, bu
when I have more than one spreadsheet open this causes a problem as AL
of the reports return the same month. :(

I'm using Excel 2000 with Windows 2K.

Thanks,
To
 
The workbook has to be saved, use it with a cell reference

=CELL("filename",A1)

It's not a bug
 
Tom,

It isn't a bug. The CELL function takes the file name from the
workbook that is active when calculation is run, not the workbook
that contains the formula. To prevent this, use the second
argument to CELL, specifying a cell (it doesn't matter what cell)
on the sheet containing the formula. E.g.,

=CELL("filename",A1)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
FWIW,

The folks at Redmond were a little inconsistent with the Help files on the
Cell() function.

In XL97 Help, the second argument is *not* described as optional, as its
displayed in bold.

In XL2K Help, the second argument *is* displayed as optional.

Don't recall what's in XL02 Help, and have no idea what's in XL03.

--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

Hi, I think I discovered an Excel bug with CELL("filename"). To
reproduce:

1. Create two new Excel files -- you can name them anything.
2. Enter CELL("filename") into each spreadsheet.
3. Notice that as soon as you enter this formula into the second
spreadsheet, the value in the first spreadsheet also changes to the
same filename as the second spreadsheet!

Does anybody have a workaround for this? I currently rely on this
function to extract a date from the filename to use in my report, but
when I have more than one spreadsheet open this causes a problem as ALL
of the reports return the same month. :(

I'm using Excel 2000 with Windows 2K.

Thanks,
Tom
 
The folks at Redmond were a little inconsistent with the Help files on the
Cell() function.

In XL97 Help, the second argument is *not* described as optional, as its
displayed in bold.

In XL2K Help, the second argument *is* displayed as optional.
Click to expand...
...

As a practical matter, CELL isn't much use in Excel without the second argument.
That argument was made optional long ago so that CELL could do double duty as
the replacement for both 123's @CELL and @CELLPOINTER. In 123, @CELLPOINTER was
only useful in macros. Since Excel macros work differently, the need isn't
there.
 
RD

Excel 2002 has the second argument as optional.

Took a while<g> but the CELL Function in 2003 also has second arg. as
optional.

Gord Dibben Excel MVP
 
Back
Top