sheet name in cell that is used by other formulas
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 |
sheet name in cell that is used by other formulas
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 |
sheet name in cell that is used by other formulas
Yes, the sheet names are numbers and your adjustment works great! Thanks a lot!
Robert "T. Valko" wrote: 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 |
sheet name in cell that is used by other formulas
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Robert" wrote in message ... Yes, the sheet names are numbers and your adjustment works great! Thanks a lot! Robert "T. Valko" wrote: 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 |
All times are GMT +1. The time now is 09:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com