sheet name in cell that is used by other formulas

[Robert]

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

 

[T. Valko]

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)

 

[Robert]

Yes, the sheet names are numbers and your adjustment works great! Thanks a lot!
Robert

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

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

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Yes, the sheet names are numbers and your adjustment works great! Thanks a
lot!
Robert

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

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