@donnawoman, I was trying to figure this behavior out myself and realized I'll have to make alternative plans.
The following work-around might get you going:
It's not clear which version of Excel you are using.
You can try the following as it works on Desktop editions, and probably on cloud-based versions of Office 365 as well:
For this case, don’t Use: =RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"))) - It only shows CURRENT ACTIVE Sheet Names, regardless of where you might need it in the Workbook.
"Do Use: =RIGHT(CELL(""filename"",A1),LEN(CELL(""filename"",A1))-FIND(""]"",CELL(""filename"",A1))) - It gives the data you need in the Cell Address Specified. (this case it's ""A1"").
Paste the formula in the cell where you need it and simply change the Cell Addresses ""A1"" to the address of the cell you are using it in."
Alternatively, you can create your own User Defined Function (UDF), call it whatever you want (as long as it's name doesn't clash with any existing UDFs or standard Excel functions).
To learn how to do that is not too tricky, simply reference the UDF documentation for the relevant Excel version and type you are using. Making a UDF to suit all variations of Excel will be possible, but quite a bit more involved as it depends on a number of factors not directly related to the version of Excel you might be using.
Hope it helps you or someone else.
