Does your sheet name happen to be a number or date?

If so, the MID function returns a TEXT value even if it looks like a number.

You can coerce a TEXT number to a numeric number like this:

=--MID(CELL("Filename",A1),FIND("]",CELL("filename",A1),1)+1,99)

--
Biff
Microsoft Excel MVP


"Robert" wrote in message
...
Hello,
I want a key cell (A4) to pick up the sheet name and have sucessfully done
so using the
=MID(CELL("Filename",A1),FIND("]",CELL("filename",A1),1)+1,99)
formula. However, when I do this, other lookup and vlookup formulas that
use
D4 as the lookup value for their calculations get the #N/A error unless I
manually type the sheet name into D4. Has anyone seen this? Is there a
workaround?
Excel 2007 @ home & 2003 @ work
Thanks in advance for any help,
Robert