Merging two worksheets into one and keeping the functionality

Question

I have a calculator in Excel. It uses 2 worksheets. How can I merge both worksheets into one and keep all formulas functional. For example, I have formulas in first worksheet referring to the second worksheet like this: (=Sheet2!B15+Sheet2!E27)

Now I want to cut all cells from second sheet and paste them into first one.

How can I do this?

Answer 1

I believe you wont have any problem doing this. Excel is generally quite good a automatically updating references for you.

For example if you cut cell A1 into cell B1 then a formula =A1+A2 will be updated to =B1+A2 for you.

So first try to just cut all the cells in Sheet 2 and paste them to the right of you cells in Sheet 1.If it dose have a problem with it, my best advice would be to update the top formula cells manually and drag down the 'auto fill square' to fill the range.

Thirdly you could try this:

Assume you have X columns of data in sheet1.

1. In sheet2 insert at least X New Columns to the left of Column A.
2. Check that the formulas still work.
3. Copy and paste cells from sheet2 into sheet1. The cells should go exactly where they were in the previous sheet. (i.e. Move cell Sheet2!X1 -> Sheet1!X1, this should work because of the cell you have inserted in step 1 means there is no overlap).
4. Check that the formulas still work. (They should because they will still be referencing the old data)
5. Now find replace in formulas all "Sheet2!" for "Sheet1!"
6. Check that the formulas still work.
7. You are done :)