Is there a way to 'absolute' the referred or target cell?
Absolute references are not changed when the referring cell is moved. But even with an absolute reference, if the referred cell moves the reference is updated. I want it to be dumber and continue to point to the same place even though the cell that was there moved, and pick up the content (a string) in what replaced the original cell. So, what I want is to define a ref to B5, add a new row 3 above B5, and still the ref points to B5. I haven't found a function to do this. Defining a Name doesn't help, nor does INDIRECT because both are updated to the new location of the original referred cell.
microsoft-excel worksheet-function
asked Jan 3 at 22:29
user980668user980668
1
add a comment |
Absolute references are not changed when the referring cell is moved. But even with an absolute reference, if the referred cell moves the reference is updated. I want it to be dumber and continue to point to the same place even though the cell that was there moved, and pick up the content (a string) in what replaced the original cell. So, what I want is to define a ref to B5, add a new row 3 above B5, and still the ref points to B5. I haven't found a function to do this. Defining a Name doesn't help, nor does INDIRECT because both are updated to the new location of the original referred cell.
microsoft-excel worksheet-function
asked Jan 3 at 22:29
user980668user980668
1
Welcome to SuperUser! Your post does not have enough information for us to help you.INDIRECTshould work, but without seeing how you are using it we can't give any advice. Please review site guidelines on How to Ask and then edit your post instead of adding information in a comment.
– Rey Juna
Jan 4 at 0:55
2
Possible duplicate of Stop Excel formula from changing when inserting/deleting rows
– fixer1234
Jan 4 at 6:21
add a comment |
Absolute references are not changed when the referring cell is moved. But even with an absolute reference, if the referred cell moves the reference is updated. I want it to be dumber and continue to point to the same place even though the cell that was there moved, and pick up the content (a string) in what replaced the original cell. So, what I want is to define a ref to B5, add a new row 3 above B5, and still the ref points to B5. I haven't found a function to do this. Defining a Name doesn't help, nor does INDIRECT because both are updated to the new location of the original referred cell.
microsoft-excel worksheet-function
asked Jan 3 at 22:29
user980668user980668
1
Absolute references are not changed when the referring cell is moved. But even with an absolute reference, if the referred cell moves the reference is updated. I want it to be dumber and continue to point to the same place even though the cell that was there moved, and pick up the content (a string) in what replaced the original cell. So, what I want is to define a ref to B5, add a new row 3 above B5, and still the ref points to B5. I haven't found a function to do this. Defining a Name doesn't help, nor does INDIRECT because both are updated to the new location of the original referred cell.
microsoft-excel worksheet-function
microsoft-excel worksheet-function
asked Jan 3 at 22:29
user980668user980668
1
asked Jan 3 at 22:29
user980668user980668
1
asked Jan 3 at 22:29
user980668user980668
1
asked Jan 3 at 22:29
user980668user980668
1
asked Jan 3 at 22:29
user980668user980668
1
1
Welcome to SuperUser! Your post does not have enough information for us to help you.INDIRECTshould work, but without seeing how you are using it we can't give any advice. Please review site guidelines on How to Ask and then edit your post instead of adding information in a comment.
– Rey Juna
Jan 4 at 0:55
2
Possible duplicate of Stop Excel formula from changing when inserting/deleting rows
– fixer1234
Jan 4 at 6:21
add a comment |
Welcome to SuperUser! Your post does not have enough information for us to help you.INDIRECTshould work, but without seeing how you are using it we can't give any advice. Please review site guidelines on How to Ask and then edit your post instead of adding information in a comment.
– Rey Juna
Jan 4 at 0:55
2
Possible duplicate of Stop Excel formula from changing when inserting/deleting rows
– fixer1234
Jan 4 at 6:21
Welcome to SuperUser! Your post does not have enough information for us to help you.
INDIRECT should work, but without seeing how you are using it we can't give any advice. Please review site guidelines on How to Ask and then edit your post instead of adding information in a comment.– Rey Juna
Jan 4 at 0:55
Welcome to SuperUser! Your post does not have enough information for us to help you.
INDIRECT should work, but without seeing how you are using it we can't give any advice. Please review site guidelines on How to Ask and then edit your post instead of adding information in a comment.– Rey Juna
Jan 4 at 0:55
2
2
Possible duplicate of Stop Excel formula from changing when inserting/deleting rows
– fixer1234
Jan 4 at 6:21
Possible duplicate of Stop Excel formula from changing when inserting/deleting rows
– fixer1234
Jan 4 at 6:21
add a comment |
1 Answer
1
active
oldest
votes
To point to B5 regardless of cell movements/insertions/deletions etc (or anything else) the formula =INDIRECT("B" & "5", TRUE) should work.
If you need it to work dynamically then the update should work in your favour to achieve the same, you could use =INDIRECT("R" & B3 & "C" & B4, FALSE) where the original cell (Row and Column) reference is held within cells B3 (5) and B4 (2) but correctly change if rows or columns are added before. There are many other permutations to use the INDIRECT function.
answered Jan 4 at 14:07
JB OneJB One
414
Even simpler:=INDIRECT("B5"):-)
– fixer1234
Jan 5 at 22:23
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%2f1390320%2fis-there-a-way-to-absolute-the-referred-or-target-cell%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
To point to B5 regardless of cell movements/insertions/deletions etc (or anything else) the formula =INDIRECT("B" & "5", TRUE) should work.
If you need it to work dynamically then the update should work in your favour to achieve the same, you could use =INDIRECT("R" & B3 & "C" & B4, FALSE) where the original cell (Row and Column) reference is held within cells B3 (5) and B4 (2) but correctly change if rows or columns are added before. There are many other permutations to use the INDIRECT function.
answered Jan 4 at 14:07
JB OneJB One
414
Even simpler:=INDIRECT("B5"):-)
– fixer1234
Jan 5 at 22:23
add a comment |
To point to B5 regardless of cell movements/insertions/deletions etc (or anything else) the formula =INDIRECT("B" & "5", TRUE) should work.
If you need it to work dynamically then the update should work in your favour to achieve the same, you could use =INDIRECT("R" & B3 & "C" & B4, FALSE) where the original cell (Row and Column) reference is held within cells B3 (5) and B4 (2) but correctly change if rows or columns are added before. There are many other permutations to use the INDIRECT function.
answered Jan 4 at 14:07
JB OneJB One
414
Even simpler:=INDIRECT("B5"):-)
– fixer1234
Jan 5 at 22:23
add a comment |
To point to B5 regardless of cell movements/insertions/deletions etc (or anything else) the formula =INDIRECT("B" & "5", TRUE) should work.
If you need it to work dynamically then the update should work in your favour to achieve the same, you could use =INDIRECT("R" & B3 & "C" & B4, FALSE) where the original cell (Row and Column) reference is held within cells B3 (5) and B4 (2) but correctly change if rows or columns are added before. There are many other permutations to use the INDIRECT function.
answered Jan 4 at 14:07
JB OneJB One
414
To point to B5 regardless of cell movements/insertions/deletions etc (or anything else) the formula =INDIRECT("B" & "5", TRUE) should work.
If you need it to work dynamically then the update should work in your favour to achieve the same, you could use =INDIRECT("R" & B3 & "C" & B4, FALSE) where the original cell (Row and Column) reference is held within cells B3 (5) and B4 (2) but correctly change if rows or columns are added before. There are many other permutations to use the INDIRECT function.
answered Jan 4 at 14:07
JB OneJB One
414
answered Jan 4 at 14:07
JB OneJB One
414
answered Jan 4 at 14:07
JB OneJB One
414
answered Jan 4 at 14:07
JB OneJB One
414
414
Even simpler:=INDIRECT("B5"):-)
– fixer1234
Jan 5 at 22:23
add a comment |
Even simpler:=INDIRECT("B5"):-)
– fixer1234
Jan 5 at 22:23
Even simpler:
=INDIRECT("B5") :-)– fixer1234
Jan 5 at 22:23
Even simpler:
=INDIRECT("B5") :-)– fixer1234
Jan 5 at 22:23
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%2f1390320%2fis-there-a-way-to-absolute-the-referred-or-target-cell%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
Welcome to SuperUser! Your post does not have enough information for us to help you.
INDIRECTshould work, but without seeing how you are using it we can't give any advice. Please review site guidelines on How to Ask and then edit your post instead of adding information in a comment.– Rey Juna
Jan 4 at 0:55
2
Possible duplicate of Stop Excel formula from changing when inserting/deleting rows
– fixer1234
Jan 4 at 6:21