How do I copy a whole page from a spreadsheet into another spreadsheet so that when the original spreadsheet is changed, the copied page gets changed in the new spreadsheet?
Replies (16)
Please login or register to join the discussion.
Can you make a copy of the tab (right click on tab lable then "Move or copy" and create a copy in a new book?
That won't work unless the formulas are all absolute and reference the filename as well as the cells.
Instead, do the move/copy, creating a copy of the spreadsheet. Then highlight the whole thing and delete it. Then go to cell A1 in the new copy, hit = and go back to the original spreadsheet and click on cell A1, then press enter.
The formula it creates will use absolute references ($A$1) - something like ='[Original spreadsheet.xlsx]Sheet1'!$A$1
Delete the dollar signs from the cell reference, then copy it to all cells, copying formulas only but not formatting, you should then have a spreadsheet that matches the original exactly, and updates when it updates.
The first time, right click on the tab you want to copy over, select move or copy, pick new book from the drop down and tick the create a copy checkbox.
Then save the new spreadsheet as whatever and keep it open. When copying the sheets from the rest follow the same process, but instead of picking "new book" pick the spreadsheet you created.
If she had done what you said above it would not have achieved what she asked for in her first post. Which was what I said above.
You were just copying the spreadsheet, nothing would have updated in it when the other ones changed.
Then what? It was wrong the first time I read it and it's still wrong.
Copying the spreadsheet, using the method you've described, does not produce a copy that updates automatically whenever the original spreadsheet is updated. It just creates a separate copy requiring separate updating, which is useless.
Stop being so arrogant and re-read my damned post. At no point did I say make a copy of the spreadsheet. I said right click on the tab and make a copy.
Copy sheet 1 as normal.
Paste in sheet 2 using "paste link" - which is the 6th icon on the paste button on my laptop.
As gainsborough says.
However beware a change in structure in the original as this will not be reflected unless the sheets are grouped first and even then the result should be checked with care.
A change in structure in the original if the copy is in a separate workbook will not be reflected at all.
Depending on your purpose, it might be worth exploring the camera option which will reflect another sheet and pick up all changes even in a separate workbook. In the latter case, even this is not risk free if the location of the source file changes.
You may be able to tell that I hate linked files.