Mixing
Is it possible to change a reference in a cell depending on the cell collecting data?
I have a column with formulas(column I) which finds a specific cell in a tabel based on two cells(Columns G and H) and using LOOKUP and MATCH. All cells in the table have different formulas(tableFormulas) to get a value, but they are very similar and all have the same reference to another cell(A1), the reference is within an IF-function which checks if it equals 1.
I would like, if possible, to change the reference to A1 in the tableFormulas too refer to a cell in column J relative to the formulas in column I. So basically I would like to use one cell but change a reference depending on the cell collecting the data.
Here is an Example image of my problem since the whole thing is a bit bigger, more spread out and not in English.
I have looked for similar problems but found nothing, however I found out that maybe the functions INDIRECT or possibly CELL or ADDRESS could work. The only other way I can think of to do it is to have 27 tables ( yes thats how many cells/rows I have in columns G to J) but that requires much more work.
I hope this makes sense, cause I am not very good at explaining, so feel free to ask anything. I am very grateful for any help!
EDIT: I found a way too not having to duplicate and change every formula 27 times, but not the way I hoped. I just made 2 tables since the cell A1 or column J that I wanted to change reference, depending on the cell collecting the data, are just supposed to be 1 or 2. Then I could just use IF in the formulas in column I to check if the value in column J is either 1 or 2. However if anyone knows if it is possible to solve my original problem, that would be great since it makes it easier if the values in Column J could be more.
excel
asked Nov 21 '18 at 15:41
JohanJohan
12
add a comment |
I have a column with formulas(column I) which finds a specific cell in a tabel based on two cells(Columns G and H) and using LOOKUP and MATCH. All cells in the table have different formulas(tableFormulas) to get a value, but they are very similar and all have the same reference to another cell(A1), the reference is within an IF-function which checks if it equals 1.
I would like, if possible, to change the reference to A1 in the tableFormulas too refer to a cell in column J relative to the formulas in column I. So basically I would like to use one cell but change a reference depending on the cell collecting the data.
Here is an Example image of my problem since the whole thing is a bit bigger, more spread out and not in English.
I have looked for similar problems but found nothing, however I found out that maybe the functions INDIRECT or possibly CELL or ADDRESS could work. The only other way I can think of to do it is to have 27 tables ( yes thats how many cells/rows I have in columns G to J) but that requires much more work.
I hope this makes sense, cause I am not very good at explaining, so feel free to ask anything. I am very grateful for any help!
EDIT: I found a way too not having to duplicate and change every formula 27 times, but not the way I hoped. I just made 2 tables since the cell A1 or column J that I wanted to change reference, depending on the cell collecting the data, are just supposed to be 1 or 2. Then I could just use IF in the formulas in column I to check if the value in column J is either 1 or 2. However if anyone knows if it is possible to solve my original problem, that would be great since it makes it easier if the values in Column J could be more.
excel
asked Nov 21 '18 at 15:41
JohanJohan
12
add a comment |
I have a column with formulas(column I) which finds a specific cell in a tabel based on two cells(Columns G and H) and using LOOKUP and MATCH. All cells in the table have different formulas(tableFormulas) to get a value, but they are very similar and all have the same reference to another cell(A1), the reference is within an IF-function which checks if it equals 1.
I would like, if possible, to change the reference to A1 in the tableFormulas too refer to a cell in column J relative to the formulas in column I. So basically I would like to use one cell but change a reference depending on the cell collecting the data.
Here is an Example image of my problem since the whole thing is a bit bigger, more spread out and not in English.
I have looked for similar problems but found nothing, however I found out that maybe the functions INDIRECT or possibly CELL or ADDRESS could work. The only other way I can think of to do it is to have 27 tables ( yes thats how many cells/rows I have in columns G to J) but that requires much more work.
I hope this makes sense, cause I am not very good at explaining, so feel free to ask anything. I am very grateful for any help!
EDIT: I found a way too not having to duplicate and change every formula 27 times, but not the way I hoped. I just made 2 tables since the cell A1 or column J that I wanted to change reference, depending on the cell collecting the data, are just supposed to be 1 or 2. Then I could just use IF in the formulas in column I to check if the value in column J is either 1 or 2. However if anyone knows if it is possible to solve my original problem, that would be great since it makes it easier if the values in Column J could be more.
excel
asked Nov 21 '18 at 15:41
JohanJohan
12
I have a column with formulas(column I) which finds a specific cell in a tabel based on two cells(Columns G and H) and using LOOKUP and MATCH. All cells in the table have different formulas(tableFormulas) to get a value, but they are very similar and all have the same reference to another cell(A1), the reference is within an IF-function which checks if it equals 1.
I would like, if possible, to change the reference to A1 in the tableFormulas too refer to a cell in column J relative to the formulas in column I. So basically I would like to use one cell but change a reference depending on the cell collecting the data.
Here is an Example image of my problem since the whole thing is a bit bigger, more spread out and not in English.
I have looked for similar problems but found nothing, however I found out that maybe the functions INDIRECT or possibly CELL or ADDRESS could work. The only other way I can think of to do it is to have 27 tables ( yes thats how many cells/rows I have in columns G to J) but that requires much more work.
I hope this makes sense, cause I am not very good at explaining, so feel free to ask anything. I am very grateful for any help!
EDIT: I found a way too not having to duplicate and change every formula 27 times, but not the way I hoped. I just made 2 tables since the cell A1 or column J that I wanted to change reference, depending on the cell collecting the data, are just supposed to be 1 or 2. Then I could just use IF in the formulas in column I to check if the value in column J is either 1 or 2. However if anyone knows if it is possible to solve my original problem, that would be great since it makes it easier if the values in Column J could be more.
excel
excel
asked Nov 21 '18 at 15:41
JohanJohan
12
asked Nov 21 '18 at 15:41
JohanJohan
12
edited Nov 22 '18 at 9:48
Johan
asked Nov 21 '18 at 15:41
JohanJohan
12
asked Nov 21 '18 at 15:41
JohanJohan
12
asked Nov 21 '18 at 15:41
JohanJohan
12
12
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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%2fstackoverflow.com%2fquestions%2f53415621%2fis-it-possible-to-change-a-reference-in-a-cell-depending-on-the-cell-collecting%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f53415621%2fis-it-possible-to-change-a-reference-in-a-cell-depending-on-the-cell-collecting%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
