sheet name in cell that is used by other formulas

R

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

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)
 
R

Robert

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

T. Valko said:
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 said:
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

T. Valko

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


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

T. Valko said:
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 said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top