SOLVED

Cell reference containing file name changes when opening second file

%3CLINGO-SUB%20id%3D%22lingo-sub-2417030%22%20slang%3D%22en-US%22%3ECell%20reference%20containing%20file%20name%20changes%20when%20opening%20second%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2417030%22%20slang%3D%22en-US%22%3E%3CP%3EWhen%20I%20open%20an%20excel%20file%20with%20a%20cell%20reference%20that%20contains%20a%20filename%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DLEFT(MID(%40CELL(%22filename%22)%2CSEARCH(%22%5B%22%2C%40CELL(%22filename%22))%2B1%2C%20SEARCH(%22%5D%22%2C%40CELL(%22filename%22))-SEARCH(%22%5B%22%2C%40CELL(%22filename%22))-1)%2C75)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20I%20open%20a%20second%20excel%20file%2C%20the%20cell%20contents%20visible%20in%20the%20first%20sheet%20inherit%20the%20file%20name%20of%20the%20second%20excel%20file%20I%20open.%26nbsp%3B%20I%20fix%20it%20by%20typing%20'Ctrl%20%2B%20%3B'%20to%20put%20a%20date%20in%20another%20cell%20just%20so%20it%20changes%20back%20to%20what%20it%20is%20supposed%20to%20be.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20a%20bug%20in%20the%20software%2C%20or%20can%20the%20syntax%20in%20the%20cell%20be%20made%20different%20so%20that%20it%20doesn't%20behave%20this%20way%3F%20Is%20there%20a%20way%20for%20the%20cells%20to%20update%20by%20themselves%20when%20switching%20between%20files%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2417030%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2417365%22%20slang%3D%22en-US%22%3ERe%3A%20Cell%20reference%20containing%20file%20name%20changes%20when%20opening%20second%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2417365%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F418083%22%20target%3D%22_blank%22%3E%40Mike_G6%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EChange%20the%20formula%20to%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DLEFT(MID(CELL(%22filename%22%2CA1)%2CSEARCH(%22%5B%22%2CCELL(%22filename%22%2CA1))%2B1%2C%20SEARCH(%22%5D%22%2CCELL(%22filename%22%2CA1))-SEARCH(%22%5B%22%2CCELL(%22filename%22%2CA1))-1)%2C75)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20CELL%20function%20now%20refers%20to%20a%20cell%20in%20the%20sheet%20containing%20the%20formula%2C%20hence%20to%20the%20workbook%20containing%20the%20formula.%20So%20it%20won't%20change%20when%20you%20activate%20another%20workbook.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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 (New 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.