Filename giving me strange results

[brianfosterblack]

I am using the following formula to show the filename in my workbook named "Mailer", which I then use in my code

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)

I have another workbook with the Name "Members" with the same formula in it.
If I have the Mailer workbook open and then also open the Member Workbook at the same time, my formula in The Mailer workbook changes it result to give the workbook name as Members
The formula in both workbooks gives the result as a Members

Anyone know what is happening and how to prevent this. The result I need is for the formula in each workbook to provide the workbook name without the .xlsm on the end of the name

I can currently achieving that but only if one workbook is open at a time.

 

[Anthony47]

You need to assign a firm reference to all those CELL...

=MID(CELL("filename",A1),SEARCH("[",CELL("filename",A1)etc etc

Otherwise its result is based on which is the current selection

If your version is Office 365 then your formula can be simplified using the function LET

Bye

 

[brianfosterblack]

You need to assign a firm reference to all those CELL...

=MID(CELL("filename",A1),SEARCH("[",CELL("filename",A1)etc etc

Otherwise its result is based on which is the current selection

If your version is Office 365 then your formula can be simplified using the function LET

Bye

Thank you. This resolves the problem. I am using 365 but not all others using the workbook are.