Formulas that reference a separate sheet updates with copy of file

Question

Hi all,

I was hoping you might be able to help with a problem I'm having!

I have a spreadsheet (Spreadsheet 1) in which there a numerous formulae which reference a separate spreadsheet (Spreadsheet 2). If a copy of Spreadsheet 2 is made through "save as" rather than through copy and pasting (lets call this Spreadsheet 2.1), the formulae in Spreadsheet 1 update to refer to Spreadsheet 2.1 rather than Spreadsheet 2. Is there anyway to stop this from happening? Alternatively, does anyone have a workaround to achieve the desired effect of Spreadsheet 1 formulae always referring to spreadsheet 2?

I am not the sole user of Spreadsheet 2 so I cannot prevent others from saving copies of the file.

I have considered recording the filepath to the original document in a cell and referencing it using INDIRECT() but this requires the referenced document to be open.

Any suggestions would be very much appreciated!

Answer 1

There is no such way to prevent it from saving as another file.

You may change it back to previous file from Workbook links> Change Source.

Answer 2

Dear anjbanj,

Thank you for posting in the Microsoft Community. We will be happy to help you. We are investigating your situation and will update the thread shortly.

I appreciate your patience and understanding and appreciate your time and cooperation.

Sincerely

Hailey