Jun 04 2021 10:23 AM
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?
Jun 04 2021 11:20 AM
SolutionChange 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.
Jun 04 2021 12:26 PM
@Hans Vogelaar thanks! That works well.
Jun 04 2021 11:20 AM
SolutionChange 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.