Could someone help me with excel

formula

Didn't find your answer?

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.

avatar
By bettybobbymeggie
04th May 2020 09:41

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?

Thanks (0)
Replying to bettybobbymeggie:
By Duggimon
04th May 2020 10:04

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.

Thanks (1)
Replying to Duggimon:
avatar
By LyneT
04th May 2020 10:18

When you say "create a copy of the the spreadsheet", do you mean "save as" copy 2? Because I have about 20 different spreadsheets with several pages on each.
I would like to create a whole new spreadsheet that contains a page from each sheet which will update when I update the original spreadsheet.

Thanks (0)
Replying to LyneT:
By Duggimon
04th May 2020 10:27

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.

Thanks (0)
Replying to Duggimon:
avatar
By bettybobbymeggie
04th May 2020 10:38

Which is exactly what I said above.

Thanks (0)
Replying to bettybobbymeggie:
By Duggimon
04th May 2020 10:41

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.

Thanks (0)
Replying to Duggimon:
avatar
By bettybobbymeggie
04th May 2020 10:43

Maybe read my comment again

Thanks (0)
Replying to bettybobbymeggie:
By Duggimon
04th May 2020 11:17

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.

Thanks (0)
Replying to Duggimon:
avatar
By bettybobbymeggie
04th May 2020 11:25

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.

Thanks (0)
Replying to Duggimon:
avatar
By LyneT
04th May 2020 13:23

Duggimon wrote:

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.


I have done this, as you suggest, but it is not updating when I update the source data.
What could I be doing wrong?
Thanks (0)
Replying to LyneT:
avatar
By alfredpennypinch
04th May 2020 13:32

Is the data in your new spreadsheet made up entirely of links to the original data?

Thanks (0)
Replying to alfredpennypinch:
avatar
By LyneT
04th May 2020 13:41

alfredpennypinch wrote:

Is the data in your new spreadsheet made up entirely of links to the original data?


Some of the data in the source sheet come from data in other sheets in the original spreadsheet and some data is inserted only in that sheet and comes from nowhere else.
Thanks (0)
avatar
By LyneT
04th May 2020 10:01

If I do that, it does not change in the new spreadsheet when I change the original.

Thanks (0)
avatar
By SXGuy
04th May 2020 10:29

Literally 3 seconds to google the answer

google "update 2 workbooks at the same time"

Thanks (0)
Scooby
By gainsborough
04th May 2020 10:32

Copy sheet 1 as normal.
Paste in sheet 2 using "paste link" - which is the 6th icon on the paste button on my laptop.

Thanks (0)
avatar
By paulwakefield1
04th May 2020 14:30

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.

Thanks (0)