Forum Discussion
Solved
Cell reference containing file name changes when opening second file
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"))-...
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.
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.
HansVogelaar thanks! That works well.