Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to copy formulas sheet to sheet in excell | Excel Worksheet Functions | |||
link formulas sheet 1 to same cells for sheet 1,2,3 etc | Excel Worksheet Functions | |||
Using data in cell for sheet referencing in formulas | Excel Discussion (Misc queries) | |||
Moving Formulas to a different sheet | Excel Discussion (Misc queries) | |||
sheet name in formulas | Excel Discussion (Misc queries) |