Excel adding original path and filename to formula referencing another worksheet within the same file when creating duplicate file copy

I have regular issues when creating copies of a file, where the formulas will seemingly randomly decide to reference the original file, rather than the Sheet and Cell within the new file.  I need to create an end of month duplicate of each of my project files.

I use a Project_Details tab/worksheet in excel to contain header information and general info for a project. eg, Customer name and address, and Estimated Project Value or other figures I will use in calculations in multiple places in different tabs/worksheets throughout the workbook/file.

As these files are passed up to a manager for end of month records, I create a duplicate of my file with the year and month reference. However, this will occasionally and seemingly randomly update the reference formulas from (for example):

‘Project_Details’!$C$12

to

‘[c:documentsProj_1234-Client_ABC-2024.05]Project_Details’!$C$12

Where [c:documentsProj_1234-Client_ABC-2024.05] is the file for last month and is in the CURRENT file “c:documentsProj_1234-Client_ABC-Current”.  So the Current file now looks to the May 2024 file for info.  

How do I stop this? Can I force Excel to not do this without having to Find and Replace file names in every copy of the file? 

Is there an order that worksheets are copied and formulas updated when copying a formula that defaults to the original file reference if the referenced worksheet isn’t in the new file yet?  

​Excel adding original path and filename to formula referencing another worksheet within the same file when creating duplicate file copy I have regular issues when creating copies of a file, where the formulas will seemingly randomly decide to reference the original file, rather than the Sheet and Cell within the new file.  I need to create an end of month duplicate of each of my project files.I use a Project_Details tab/worksheet in excel to contain header information and general info for a project. eg, Customer name and address, and Estimated Project Value or other figures I will use in calculations in multiple places in different tabs/worksheets throughout the workbook/file.As these files are passed up to a manager for end of month records, I create a duplicate of my file with the year and month reference. However, this will occasionally and seemingly randomly update the reference formulas from (for example):’Project_Details’!$C$12to'[c:documentsProj_1234-Client_ABC-2024.05]Project_Details’!$C$12Where [c:documentsProj_1234-Client_ABC-2024.05] is the file for last month and is in the CURRENT file “c:documentsProj_1234-Client_ABC-Current”.  So the Current file now looks to the May 2024 file for info.   How do I stop this? Can I force Excel to not do this without having to Find and Replace file names in every copy of the file? Is there an order that worksheets are copied and formulas updated when copying a formula that defaults to the original file reference if the referenced worksheet isn’t in the new file yet?    Read More

​