Incorrect filename displayed when multiple files opened

I used this formula, from Excel help, to show the current name of a file.

 

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

 

It works, but I have a problem.  If I have multiple files open, the formula returns the filename of whichever file was worked in last.  So, if I start working in a second file, it shows that filename instead of the name of the file that the cell is located in.  Is there a way to get a cell to always show the name of the file that it is in, even when working in a second file?

 

Question Info


Last updated April 2, 2020 Views 521 Applies to:
Answer
Answer

Change the CELL function to include the second argument, which is a cell reference for the file of interest:

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

will always show the name of the file that it is in rather than the active file name.

HTH,
Bernie

7 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.