Cell referencing sheet name

[patsfan]

I use this formula in cell [A1] of sheet named "Virginia" "=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)" to place the sheet name in that cell.

When I enter the formula into cell [A1] of a new sheet "Maryland", the formula may pick up the name of "Virginia".

Likewise, the formula on sheet "Virginia" may pick up the name "Maryland".


Does anyone know why this occurs and/or how I can prevent this?


Thanks

 

[bobhc]

Good day patsfan


There isn't a single function to return the sheet name, but you can use a combination of functions to get at it. Try this one


=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"),1))


Edit...please ignore, same result as you, sheet names are as should be when first entering the formula but if you switch to other sheets the previous sheet takes the name from the new sheet when the formula is pasted. F9 will update sheet to correct name but even saving the file and reopening makes no difference....need to find out where it is going wrong.

 

[Haseeb A]

Hello Patsfan,


Use a reference in CELL function ie A1. Excel help says, "reference Optional. The cell that you want information about. If omitted, the information specified in the info_type argument is returned for the last cell that was changed"


So try this:


=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)


This way reference always will be 'CurrentSheet'!A1

 

[patsfan]

Thanks Haseeb. Works like a charm