Excel 2007 when I Cut & Paste =K62 to a new worksheet and I get =Scenario!K62. When I copy and Paste it I get =REF!.

How can I get Copy & Paste to work the same way as Cut & Paste does?

  • 1
    Have you tried this MS Office Support Article - Move or Copy Formula? – CharlieRB Jul 12 '13 at 19:47
  • Yar, but that is talking about keeping things in the same worksheet. This is moving to a new worksheet, when I cut & paste it auto adds in the reference link "Scenario!" when I Copy&Paste it just turns into REF!. I don't want to go back and add in "Scenario!" to every equation I need to copy but I don't want to cut as I still need them in that current worksheet. – DanTheMan Jul 12 '13 at 20:40
  • copy, right click, paste special, paste special, Formulas, OK Have you tried this? Beware, when you want to do something like this, sometimes you need the sheets to be identical, for instance if H24 = D9, you'll want to paste formula in H24 on the other sheet, because the reference is relative. Try using H24 = $D$9 – Raystafarian Jul 13 '13 at 8:43

Copy and paste brings over the relative references in a formula to the new sheet.

For example, say you have the formula = A2 + G5 in cell E2.

What get copied over to the new worksheet is "take the cell four columns to the left of the cell the formula is in and add it to the cell two columns to the right and three cells down.

If you copy the formula into a cell in the new sheet that puts one of those references "off the sheet", cell A2, for example, you get the REF! error.

Cut and paste, on the other hand, preserves the absolute references in the old sheet when you copy it to the new sheet.

This is the built-in behavior for both these actions.

  • Is there any possible way to make the Copy & Paste function preserve the absolute references? – DanTheMan Jul 13 '13 at 5:07
  • You can cut and paste the formula into the new sheet, put the cell into edit mode (F2 key), highlight and copy the formula (still in the new sheet), and paste it back into the old sheet. There are also more elaborate ways to accomplish this for a range of formulas (see here, for example, but it's all a bit clunky. I assume it would be possible to program a way in VBA, but I haven't tried it. – chuff Jul 13 '13 at 5:43

I finally figured out a (not so great) workaround:

  1. Make a copy of the sheet that you want to copy from
  2. Cut the cells (instead of copy)
  3. Paste
  4. Delete the copy of the sheet (which is now missing the information)

As long as you are not also referencing those cells elsewhere, it should work!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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