How do I get Copy & Paste to work like Cut & Paste with formulas in Excel 2007?
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?
microsoft-excel microsoft-excel-2007 worksheet-function cut-and-paste
edited Jul 12 '13 at 19:31
CharlieRB
20.6k44492
asked Jul 12 '13 at 19:05
DanTheManDanTheMan
11
add a comment |
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?
microsoft-excel microsoft-excel-2007 worksheet-function cut-and-paste
edited Jul 12 '13 at 19:31
CharlieRB
20.6k44492
asked Jul 12 '13 at 19:05
DanTheManDanTheMan
11
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
add a comment |
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?
microsoft-excel microsoft-excel-2007 worksheet-function cut-and-paste
edited Jul 12 '13 at 19:31
CharlieRB
20.6k44492
asked Jul 12 '13 at 19:05
DanTheManDanTheMan
11
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?
microsoft-excel microsoft-excel-2007 worksheet-function cut-and-paste
microsoft-excel microsoft-excel-2007 worksheet-function cut-and-paste
edited Jul 12 '13 at 19:31
CharlieRB
20.6k44492
asked Jul 12 '13 at 19:05
DanTheManDanTheMan
11
edited Jul 12 '13 at 19:31
CharlieRB
20.6k44492
asked Jul 12 '13 at 19:05
DanTheManDanTheMan
11
edited Jul 12 '13 at 19:31
CharlieRB
20.6k44492
edited Jul 12 '13 at 19:31
CharlieRB
20.6k44492
edited Jul 12 '13 at 19:31
CharlieRB
20.6k44492
20.6k44492
asked Jul 12 '13 at 19:05
DanTheManDanTheMan
11
asked Jul 12 '13 at 19:05
DanTheManDanTheMan
11
asked Jul 12 '13 at 19:05
DanTheManDanTheMan
11
11
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
add a comment |
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
1
1
Have you tried this MS Office Support Article - Move or Copy Formula?
– CharlieRB
Jul 12 '13 at 19:47
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
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, 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
add a comment |
2 Answers
2
active
oldest
votes
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.
answered Jul 13 '13 at 1:29
chuffchuff
3,12411017
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
add a comment |
I finally figured out a (not so great) workaround:
- Make a copy of the sheet that you want to copy from
- Cut the cells (instead of copy)
- Paste
- 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!
answered Aug 6 '18 at 21:46
ShaneShane
1
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f619104%2fhow-do-i-get-copy-paste-to-work-like-cut-paste-with-formulas-in-excel-2007%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
answered Jul 13 '13 at 1:29
chuffchuff
3,12411017
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
add a comment |
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.
answered Jul 13 '13 at 1:29
chuffchuff
3,12411017
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
add a comment |
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.
answered Jul 13 '13 at 1:29
chuffchuff
3,12411017
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.
answered Jul 13 '13 at 1:29
chuffchuff
3,12411017
edited Jul 13 '13 at 3:08
answered Jul 13 '13 at 1:29
chuffchuff
3,12411017
answered Jul 13 '13 at 1:29
chuffchuff
3,12411017
answered Jul 13 '13 at 1:29
chuffchuff
3,12411017
3,12411017
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
add a comment |
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
Is there any possible way to make the Copy & Paste function preserve the absolute references?
– DanTheMan
Jul 13 '13 at 5:07
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
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
add a comment |
I finally figured out a (not so great) workaround:
- Make a copy of the sheet that you want to copy from
- Cut the cells (instead of copy)
- Paste
- 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!
answered Aug 6 '18 at 21:46
ShaneShane
1
add a comment |
I finally figured out a (not so great) workaround:
- Make a copy of the sheet that you want to copy from
- Cut the cells (instead of copy)
- Paste
- 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!
answered Aug 6 '18 at 21:46
ShaneShane
1
add a comment |
I finally figured out a (not so great) workaround:
- Make a copy of the sheet that you want to copy from
- Cut the cells (instead of copy)
- Paste
- 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!
answered Aug 6 '18 at 21:46
ShaneShane
1
I finally figured out a (not so great) workaround:
- Make a copy of the sheet that you want to copy from
- Cut the cells (instead of copy)
- Paste
- 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!
answered Aug 6 '18 at 21:46
ShaneShane
1
answered Aug 6 '18 at 21:46
ShaneShane
1
answered Aug 6 '18 at 21:46
ShaneShane
1
answered Aug 6 '18 at 21:46
ShaneShane
1
1
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f619104%2fhow-do-i-get-copy-paste-to-work-like-cut-paste-with-formulas-in-excel-2007%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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