Occasionally you have spreadsheets that are linked together but you need to merge spreadsheets into one workbook and you want all the links to be maintained (without having to re-link everything).
This is very easy to do in Excel.
Open the two or more linked files. You will notice that one or all of the files contain formulas that look like the red circle below. The square brackets ([ ]) indicate that this spreadsheet is looking outside of itself at another spreadsheet (called ‘Input Data v2’ in this case)
To merge the workbooks and keep all the links intact, go to one of the spreadsheets and MOVE the sheet or sheets out of the one workbook into the other workbook. You achieve this by right clicking on the sheet name and choosing the Move or Copy option as shown below.
You will then see this screen. If you click on the dropdown you can choose where you want to MOVE the sheet to, being one of the open spreadsheets or to a new spreadsheet. Choose the correct spreadsheet. Note that you leave the Copy tick box at the bottom blank. You do not want a copy as the links will be lost. You need to move it.
When you click OK, you will see that the sheet is moved into the other spreadsheet (it maintains its sheet name unless there is a duplicate in which case Excel adds a number to it).
If you look at the original formula that previusly looked at the other workbook, you will notice that it has been updated to look at the sheet within this spreadsheet.
You can continue to do this with each sheet or you can highlight all the sheets and move them into another workbook.
If you move all the sheets from a workbook, it will disappear from Excel but it is still saved. In this way you can merge spreadsheets into one workbook while maintaining all cross links.
Related
Break up Excel spreadsheet workbooks
How to merge cells in Excel (and why not to)
Merge multiple csv files into a single file for use in Excel