Excel: Merging two files/Copy-Pasting from one file to another

[Nickel020]

Hi,
I am working on a university assignment in a group, and different people work on the same excel file at the same time sometime and we need to combine the results.
Essentially, I am working on a sheet that links to other sheets in that file and I want to copy my work into a combined file with the other people's work on different sheets.

For example this is one of the many formulas in my sheet that I want to copy:

=('Balance Sheet'!B47+'Balance Sheet'!B56-'Balance Sheet'!B25)/'Cash Flow Statement'!B12

But when I any copy and paste the whole sheet I don't get the above formula (which I want) but I get a formula that links to the other file from which I am copying:

=('[Demag-Debt1V2.xls]Balance Sheet'!B47+'[Demag-Debt1V2.xls]Balance Sheet'!B56-'[Demag-Debt1V2.xls]Balance Sheet'!B25)/'[Demag-Debt1V2.xls]Cash Flow Statement'!B12

In essence, I want to get rid of all the "[Demag-Debt1V2.xls]" type references but doing so manually would take a good amount of time.

Can anyone tell me how I can straight out copy exactly the formulas without excel adding the "[Demag-Debt1V2.xls]" ?

Thanks!

 

[tw1164]

If you're all using the same shared workbook, you can merge the changes into a workbook.

 

[Nickel020]

I don't have an English Office Version, I assume the "tabs" for the different spreadsheets above/next to the bottom scroll bar are called "sheets" (not sure if they're called sheets, pages or something else...)

We basically start off with the same file, but everyone has it on his own PC (no networking etc.). We then make changes to that file, usually everyone on a different sheet of the file. E.g. there are 10 sheets in the workbook and i make changes to sheet 5 while he makes changes to sheet 1. We then want to copy my changes on sheet 5 into his file so we have an updated file with the changes to sheet 5 and sheet 1. But if we do that by just using Ctrl+C and Ctrl+V we encounter the above described Problem.

 

[Thyme]

Is there anything in the smart tag that comes up after you paste?

 

[mayest]

Originally posted by: Nickel020
In essence, I want to get rid of all the "[Demag-Debt1V2.xls]" type references but doing so manually would take a good amount of time.

Can anyone tell me how I can straight out copy exactly the formulas without excel adding the "[Demag-Debt1V2.xls]" ?

You can do this with Edit --> Replace. In the "Find what:" box just type in "[*]" (no quotes) and leave the "Replace with:" box empty. That will get rid of all links to external sheets.

So, '[Demag-Debt1V2.xls]Sheet1'!A1 will become Sheet1!A1.

Edit: Note that you might need to be careful with this if you don't want to break all of the external links in the worksheet.

 

[tehach]

What Mayest suggests is a good solution. But be careful that the references are in exactly the same places as on your target file, and the target worksheet is named exactly the same as what's in the reference.

 

[Nickel020]

Thanks! That will do the job =)