Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
1  
From what I understand, most Excel questions on SO involve programmatic involvement with Excel, rather than just how to perform a task in Excel. I suppose I could be wrong, I don't spend much time in the [excel] tag. However, this question probably fits better on Super User, per this and this. –  Rob Hruska Sep 21 '11 at 18:40
    
@avetarman - what happened when you tried that? Did it not work? –  Tim Williams Sep 21 '11 at 18:46
add comment

migrated from stackoverflow.com Sep 22 '11 at 15:27

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 2 down vote accepted

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 :)
share|improve this answer
    
Thanks for your reply. It works within the same worksheet. But I cannot bring all cells from Sheet2 to Sheet1 with automatically changing references in Sheet1. And there are a lot of cells. –  avetarman Sep 21 '11 at 19:06
    
Ok ill update my answer, I have another idea. –  Robert Sep 21 '11 at 20:15
    
Thanks again, Robert. As I stated before, I don't want to do that manually. But it seems there is no other method (No native MS method). –  avetarman Sep 23 '11 at 21:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.