How to retain the original value when a referenced cell changes in Excel?
I'm having a problem in MS-Excel. If I entered a value in sheet1 cell A1, that value appears everywhere that cell is referenced. Is it possible to retain that value in the referencing cells if I erase the value in sheet1!A1?
microsoft-excel
edited Oct 11 '15 at 1:57
fixer1234
17.7k144581
asked Oct 10 '15 at 8:43
Muralidhara Rao Bongu
111
add a comment |
I'm having a problem in MS-Excel. If I entered a value in sheet1 cell A1, that value appears everywhere that cell is referenced. Is it possible to retain that value in the referencing cells if I erase the value in sheet1!A1?
microsoft-excel
edited Oct 11 '15 at 1:57
fixer1234
17.7k144581
asked Oct 10 '15 at 8:43
Muralidhara Rao Bongu
111
add a comment |
I'm having a problem in MS-Excel. If I entered a value in sheet1 cell A1, that value appears everywhere that cell is referenced. Is it possible to retain that value in the referencing cells if I erase the value in sheet1!A1?
microsoft-excel
edited Oct 11 '15 at 1:57
fixer1234
17.7k144581
asked Oct 10 '15 at 8:43
Muralidhara Rao Bongu
111
I'm having a problem in MS-Excel. If I entered a value in sheet1 cell A1, that value appears everywhere that cell is referenced. Is it possible to retain that value in the referencing cells if I erase the value in sheet1!A1?
microsoft-excel
microsoft-excel
edited Oct 11 '15 at 1:57
fixer1234
17.7k144581
asked Oct 10 '15 at 8:43
Muralidhara Rao Bongu
111
edited Oct 11 '15 at 1:57
fixer1234
17.7k144581
asked Oct 10 '15 at 8:43
Muralidhara Rao Bongu
111
edited Oct 11 '15 at 1:57
fixer1234
17.7k144581
edited Oct 11 '15 at 1:57
fixer1234
17.7k144581
edited Oct 11 '15 at 1:57
fixer1234
17.7k144581
17.7k144581
asked Oct 10 '15 at 8:43
Muralidhara Rao Bongu
111
asked Oct 10 '15 at 8:43
Muralidhara Rao Bongu
111
asked Oct 10 '15 at 8:43
Muralidhara Rao Bongu
111
111
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
If you want to KEEP the text/value that is shown in a cell for "all future":
- Select the cell
- Select COPY from the menus/ribbon or hold CTRL and hit C
- Immediately select "Paste special" from the menus/ribbon,
- select "[v] Values" in the dialog that opened, then click OK
By this 'what was displayed' gets converted into the same TEXT, any formulas will vanish, and the text will stay until you delete it.
answered Oct 10 '15 at 17:59
Hannu
3,9621925
add a comment |
If you have a formula that refers to =Sheet1!A1, it will show the value of cell A1 in Sheet1. If that cell value is deleted, the formula will return a 0.
That is how Excel works. A formula is re-calculated when the cells that contribute to the formula are changed. There is no way that a formula can show a value that no longer exists in a cell.
If you want to retain a value in a cell even after its original source (in another cell) is deleted, you will need VBA instead of a formula.
You may need to explain the bigger picture and detail the business logic that you want to apply before a perfect solution can be attempted.
answered Oct 10 '15 at 8:57
teylyn
16.9k22539
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%2f984691%2fhow-to-retain-the-original-value-when-a-referenced-cell-changes-in-excel%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
If you want to KEEP the text/value that is shown in a cell for "all future":
- Select the cell
- Select COPY from the menus/ribbon or hold CTRL and hit C
- Immediately select "Paste special" from the menus/ribbon,
- select "[v] Values" in the dialog that opened, then click OK
By this 'what was displayed' gets converted into the same TEXT, any formulas will vanish, and the text will stay until you delete it.
answered Oct 10 '15 at 17:59
Hannu
3,9621925
add a comment |
If you want to KEEP the text/value that is shown in a cell for "all future":
- Select the cell
- Select COPY from the menus/ribbon or hold CTRL and hit C
- Immediately select "Paste special" from the menus/ribbon,
- select "[v] Values" in the dialog that opened, then click OK
By this 'what was displayed' gets converted into the same TEXT, any formulas will vanish, and the text will stay until you delete it.
answered Oct 10 '15 at 17:59
Hannu
3,9621925
add a comment |
If you want to KEEP the text/value that is shown in a cell for "all future":
- Select the cell
- Select COPY from the menus/ribbon or hold CTRL and hit C
- Immediately select "Paste special" from the menus/ribbon,
- select "[v] Values" in the dialog that opened, then click OK
By this 'what was displayed' gets converted into the same TEXT, any formulas will vanish, and the text will stay until you delete it.
answered Oct 10 '15 at 17:59
Hannu
3,9621925
If you want to KEEP the text/value that is shown in a cell for "all future":
- Select the cell
- Select COPY from the menus/ribbon or hold CTRL and hit C
- Immediately select "Paste special" from the menus/ribbon,
- select "[v] Values" in the dialog that opened, then click OK
By this 'what was displayed' gets converted into the same TEXT, any formulas will vanish, and the text will stay until you delete it.
answered Oct 10 '15 at 17:59
Hannu
3,9621925
answered Oct 10 '15 at 17:59
Hannu
3,9621925
answered Oct 10 '15 at 17:59
Hannu
3,9621925
answered Oct 10 '15 at 17:59
Hannu
3,9621925
3,9621925
add a comment |
add a comment |
If you have a formula that refers to =Sheet1!A1, it will show the value of cell A1 in Sheet1. If that cell value is deleted, the formula will return a 0.
That is how Excel works. A formula is re-calculated when the cells that contribute to the formula are changed. There is no way that a formula can show a value that no longer exists in a cell.
If you want to retain a value in a cell even after its original source (in another cell) is deleted, you will need VBA instead of a formula.
You may need to explain the bigger picture and detail the business logic that you want to apply before a perfect solution can be attempted.
answered Oct 10 '15 at 8:57
teylyn
16.9k22539
add a comment |
If you have a formula that refers to =Sheet1!A1, it will show the value of cell A1 in Sheet1. If that cell value is deleted, the formula will return a 0.
That is how Excel works. A formula is re-calculated when the cells that contribute to the formula are changed. There is no way that a formula can show a value that no longer exists in a cell.
If you want to retain a value in a cell even after its original source (in another cell) is deleted, you will need VBA instead of a formula.
You may need to explain the bigger picture and detail the business logic that you want to apply before a perfect solution can be attempted.
answered Oct 10 '15 at 8:57
teylyn
16.9k22539
add a comment |
If you have a formula that refers to =Sheet1!A1, it will show the value of cell A1 in Sheet1. If that cell value is deleted, the formula will return a 0.
That is how Excel works. A formula is re-calculated when the cells that contribute to the formula are changed. There is no way that a formula can show a value that no longer exists in a cell.
If you want to retain a value in a cell even after its original source (in another cell) is deleted, you will need VBA instead of a formula.
You may need to explain the bigger picture and detail the business logic that you want to apply before a perfect solution can be attempted.
answered Oct 10 '15 at 8:57
teylyn
16.9k22539
If you have a formula that refers to =Sheet1!A1, it will show the value of cell A1 in Sheet1. If that cell value is deleted, the formula will return a 0.
That is how Excel works. A formula is re-calculated when the cells that contribute to the formula are changed. There is no way that a formula can show a value that no longer exists in a cell.
If you want to retain a value in a cell even after its original source (in another cell) is deleted, you will need VBA instead of a formula.
You may need to explain the bigger picture and detail the business logic that you want to apply before a perfect solution can be attempted.
answered Oct 10 '15 at 8:57
teylyn
16.9k22539
answered Oct 10 '15 at 8:57
teylyn
16.9k22539
answered Oct 10 '15 at 8:57
teylyn
16.9k22539
answered Oct 10 '15 at 8:57
teylyn
16.9k22539
16.9k22539
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f984691%2fhow-to-retain-the-original-value-when-a-referenced-cell-changes-in-excel%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
