SOLVED

Cell reference containing file name changes when opening second file

Copper Contributor

When I open an excel file with a cell reference that contains a filename:

 

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

 

and I open a second excel file, the cell contents visible in the first sheet inherit the file name of the second excel file I open.  I fix it by typing 'Ctrl + ;' to put a date in another cell just so it changes back to what it is supposed to be.

 

Is this a bug in the software, or can the syntax in the cell be made different so that it doesn't behave this way? Is there a way for the cells to update by themselves when switching between files?

 

 

2 Replies
best response confirmed by Mike_G6 (Copper Contributor)
Solution

@Mike_G6 

Change the formula to

 

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

 

The CELL function now refers to a cell in the sheet containing the formula, hence to the workbook containing the formula. So it won't change when you activate another workbook.

1 best response

Accepted Solutions
best response confirmed by Mike_G6 (Copper Contributor)
Solution

@Mike_G6 

Change the formula to

 

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

 

The CELL function now refers to a cell in the sheet containing the formula, hence to the workbook containing the formula. So it won't change when you activate another workbook.

View solution in original post