Cross-sheet references when moving formulas to a new workbook
March 12, 2025 1:17 PM Subscribe
I have an Excel workbook that has five sheets. Most of the sheets have formulas and charts that reference other sheets in the same workbook. For reasons, I want to copy two of the sheets into a new workbook. But when I do this, Excel "helpfully" updates the formulas to refer back to the original workbook. I want them to refer to the same-named sheet in the same workbook in which they reside. Is this possible?
Workbook "Original" has sheets "Data", "More Data", "Calculations", "Graphs".
Data is information typed into the spreadsheet. No formulas.
More Data has some columns of information into the spreadsheet and some some columns calculated from values in the "Data" sheet.
Calculations collects and summarizes the data from the Data and More Data sheets.
Graphs creates Excel graphs from the info in the Calculations sheet.
I made a copy of the workbook, named it "Intern Edition" and gave it to an intern to improve the calculations and graphs. They did that.
Meanwhile, people kept entering new data into "Original" workbook.
I want to copy the "Calculations" and "Graphs" sheets from "Intern Edition" into "Original". But when I do that, the formulas and graphs in those sheets don't refer to the "Data" and "More Data" in "Original". Those sheets remember that they came from "Intern Edition" and they refer to "[Intern Edition]Data" and "[Intern Edition]More Data".
How can I move these sheets to "Original" and have their formulas refer to other sheets in "Original"?
Workbook "Original" has sheets "Data", "More Data", "Calculations", "Graphs".
Data is information typed into the spreadsheet. No formulas.
More Data has some columns of information into the spreadsheet and some some columns calculated from values in the "Data" sheet.
Calculations collects and summarizes the data from the Data and More Data sheets.
Graphs creates Excel graphs from the info in the Calculations sheet.
I made a copy of the workbook, named it "Intern Edition" and gave it to an intern to improve the calculations and graphs. They did that.
Meanwhile, people kept entering new data into "Original" workbook.
I want to copy the "Calculations" and "Graphs" sheets from "Intern Edition" into "Original". But when I do that, the formulas and graphs in those sheets don't refer to the "Data" and "More Data" in "Original". Those sheets remember that they came from "Intern Edition" and they refer to "[Intern Edition]Data" and "[Intern Edition]More Data".
How can I move these sheets to "Original" and have their formulas refer to other sheets in "Original"?
Is there a reason not to go the other way? If that data sheet has no formulas, go into the data sheet, select all, and paste it into the data sheet with the better formulas. Make sure you save copies of everything first, of course. Then save the version with the correct data and correct formulas with the name of the Original file and bob's your uncle.
posted by If only I had a penguin... at 1:51 PM on March 12
posted by If only I had a penguin... at 1:51 PM on March 12
Save As and save a duplicate the XLS file with a new name.
With the duplicate file open, delete the sheets that you no longer need (presumably all but the two you are trying to copy).
Save.
You now have those two sheets without any linkage back to the original file.
posted by NotMyselfRightNow at 2:22 PM on March 12 [8 favorites]
With the duplicate file open, delete the sheets that you no longer need (presumably all but the two you are trying to copy).
Save.
You now have those two sheets without any linkage back to the original file.
posted by NotMyselfRightNow at 2:22 PM on March 12 [8 favorites]
Came here to say what not myself said.
posted by sillysally at 7:16 PM on March 12 [1 favorite]
posted by sillysally at 7:16 PM on March 12 [1 favorite]
I just did this yesterday. I did "Find" and "Replace" and changed the sheet reference for everything.
posted by kjs4 at 11:21 PM on March 12 [2 favorites]
posted by kjs4 at 11:21 PM on March 12 [2 favorites]
Response by poster: Thanks for all these suggestions. I ended up copying the data over, as If only I had a penguin... suggested. I did have to update one formula, but that wasn't hard. I will keep this thread on tap, though, in case the situation comes up in the future.
posted by Winnie the Proust at 8:29 AM on March 13
posted by Winnie the Proust at 8:29 AM on March 13
You are not logged in, either login or create an account to post comments
posted by youknowwhatpart at 1:50 PM on March 12