How do I copy data (inc calcs) between 2 .xls

March 13, 2011 at 18:11:34
Specs: Windows 7
I have 2 MS Office .xls spreadsheets that have exactly the same headings / cell references for data etc - 2 consecutive years of the same budget.
However, one of them is missing a sheet that helps me calculate a 'running total' of all the other sheets. It's essentially a page of calculations like


I want to copy that sheet from the first spreadsheet to the second, so I've created a blank sheet on the second .xls and then pasted the info from the sheet. Sounds easy right? Unofrtunately not. When I paste the info from the first .xls to the 2nd.xls it pastes a formula like this one:


My question is how I can make it reference the spreadsheet I pasted it INTO - not the one I pasted it from. This will save me hours of work, thanks in advance for your help.

ie it's referencing the data back to the first .xls - not the one it's in.

See More: How do I copy data (inc calcs) between 2 .xls

Report •

March 13, 2011 at 18:21:54
If I'm picturing this correctly, the easiest thing to do would be to go to the second wrksht and just typ the formula in the first row and then just drag the AutoFill box (that little black back at the bottom left corner of a cell you've selected?!!!) and drag it down as far as you need it.

When you copy it from one sheet to another it tries to take the cell references in the original formula... so you just need to re-write it so that it includes the correct cell references for the second sheet

IDEA!!!!: IF, and only if, the setup between both sheets are the exact same, all you have to do is copy the formula and after you paste it into the second sheet, just manually delete the name of the first worksheet... the cell references (eg.B7) will work they are setup the same way.


Report •

March 13, 2011 at 18:25:10
Hi jrsommer.
Thanks for your suggestion.
My problem is there's 100's of different formulas on the page - so both your solutions would take me a long time.
The setup to b oth sheets is exactly the same, so if I could 'bulk delete' the reference to the old spreadsheet that would be good, but no chance of removing it off every reference.

Report •

March 13, 2011 at 18:33:49
have both excel files open ...

in the excel file with the source sheet, Edit/Move or Copy Sheet, check Make a Copy, and choose the other excel file in the list at the top

Report •

Related Solutions

March 13, 2011 at 19:57:47
Hi Dave,
That worked for some of the calculations, but there's still some that say:

=SUM('[old name.xls]National'!B7+'[old name.xls]Queensland'!B7+'[old name.xls]Tasmania'!B7+'[old name.xls]WA'!B7+'[old name.xls]NSW'!B7)

Report •

Ask Question