How to match cell value to sheet name then return value based on that
I would like to match cell value to sheet name then return value based on that. How can i do that?
For example, there are 2 column (A and B)
If the value in range A1:A5 = other sheet name
Then return cell value of column B in that other sheet name
Thank you in advance.
microsoft-excel microsoft-excel-2013
asked Jul 8 '15 at 5:12
user466900
612
add a comment |
I would like to match cell value to sheet name then return value based on that. How can i do that?
For example, there are 2 column (A and B)
If the value in range A1:A5 = other sheet name
Then return cell value of column B in that other sheet name
Thank you in advance.
microsoft-excel microsoft-excel-2013
asked Jul 8 '15 at 5:12
user466900
612
add a comment |
I would like to match cell value to sheet name then return value based on that. How can i do that?
For example, there are 2 column (A and B)
If the value in range A1:A5 = other sheet name
Then return cell value of column B in that other sheet name
Thank you in advance.
microsoft-excel microsoft-excel-2013
asked Jul 8 '15 at 5:12
user466900
612
I would like to match cell value to sheet name then return value based on that. How can i do that?
For example, there are 2 column (A and B)
If the value in range A1:A5 = other sheet name
Then return cell value of column B in that other sheet name
Thank you in advance.
microsoft-excel microsoft-excel-2013
microsoft-excel microsoft-excel-2013
asked Jul 8 '15 at 5:12
user466900
612
asked Jul 8 '15 at 5:12
user466900
612
asked Jul 8 '15 at 5:12
user466900
612
asked Jul 8 '15 at 5:12
user466900
612
asked Jul 8 '15 at 5:12
user466900
612
612
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
If A2 in Sheet1 contains the formula, and B2 contains the sheet name "Sheet2", this will fetch the value in Sheet2!A2:
=INDIRECT(B2&"!"&ADDRESS(ROW(),COLUMN()))
To get the value in Sheet2!B2 instead, you can add 1 to COLUMN():
=INDIRECT(B2&"!"&ADDRESS(ROW(),COLUMN()+1))
Explanation:
INDIRECTreturns the value stored in a cell using some text formatted as a standard cell reference such as "A2", or "Sheet2!$A$2". Now we will try to generate that text automatically.
ADDRESS(row, col)converts cell and column numbers to a standard cell reference in text format. ADDRESS(1,1) returns "$A$1", and so on.
ROW()returns the number of the row the formula sits in, andCOLUMN()for the column number. Since the formula is in A2,ROW()will equal 2 andCOLUMN()will equal 1. These are just plain numbers, so we can add 1 to the column number to get B instead of A. We give those numbers toADDRESS(2,2)to get "$B$2".- We append this address to the sheet name we want that is in B2:
B2&!"&ADDRESS(ROW(),COLUMN()), not forgetting the exclamation mark between, to obtain the full cell reference we need: "Sheet2!$B$2" - Last step, we provide that text to
INDIRECT, which will go and fetch the value at that address.
answered Jul 8 '15 at 5:54
mtone
10.8k53659
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%2f937701%2fhow-to-match-cell-value-to-sheet-name-then-return-value-based-on-that%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
If A2 in Sheet1 contains the formula, and B2 contains the sheet name "Sheet2", this will fetch the value in Sheet2!A2:
=INDIRECT(B2&"!"&ADDRESS(ROW(),COLUMN()))
To get the value in Sheet2!B2 instead, you can add 1 to COLUMN():
=INDIRECT(B2&"!"&ADDRESS(ROW(),COLUMN()+1))
Explanation:
INDIRECTreturns the value stored in a cell using some text formatted as a standard cell reference such as "A2", or "Sheet2!$A$2". Now we will try to generate that text automatically.
ADDRESS(row, col)converts cell and column numbers to a standard cell reference in text format. ADDRESS(1,1) returns "$A$1", and so on.
ROW()returns the number of the row the formula sits in, andCOLUMN()for the column number. Since the formula is in A2,ROW()will equal 2 andCOLUMN()will equal 1. These are just plain numbers, so we can add 1 to the column number to get B instead of A. We give those numbers toADDRESS(2,2)to get "$B$2".- We append this address to the sheet name we want that is in B2:
B2&!"&ADDRESS(ROW(),COLUMN()), not forgetting the exclamation mark between, to obtain the full cell reference we need: "Sheet2!$B$2" - Last step, we provide that text to
INDIRECT, which will go and fetch the value at that address.
answered Jul 8 '15 at 5:54
mtone
10.8k53659
add a comment |
If A2 in Sheet1 contains the formula, and B2 contains the sheet name "Sheet2", this will fetch the value in Sheet2!A2:
=INDIRECT(B2&"!"&ADDRESS(ROW(),COLUMN()))
To get the value in Sheet2!B2 instead, you can add 1 to COLUMN():
=INDIRECT(B2&"!"&ADDRESS(ROW(),COLUMN()+1))
Explanation:
INDIRECTreturns the value stored in a cell using some text formatted as a standard cell reference such as "A2", or "Sheet2!$A$2". Now we will try to generate that text automatically.
ADDRESS(row, col)converts cell and column numbers to a standard cell reference in text format. ADDRESS(1,1) returns "$A$1", and so on.
ROW()returns the number of the row the formula sits in, andCOLUMN()for the column number. Since the formula is in A2,ROW()will equal 2 andCOLUMN()will equal 1. These are just plain numbers, so we can add 1 to the column number to get B instead of A. We give those numbers toADDRESS(2,2)to get "$B$2".- We append this address to the sheet name we want that is in B2:
B2&!"&ADDRESS(ROW(),COLUMN()), not forgetting the exclamation mark between, to obtain the full cell reference we need: "Sheet2!$B$2" - Last step, we provide that text to
INDIRECT, which will go and fetch the value at that address.
answered Jul 8 '15 at 5:54
mtone
10.8k53659
add a comment |
If A2 in Sheet1 contains the formula, and B2 contains the sheet name "Sheet2", this will fetch the value in Sheet2!A2:
=INDIRECT(B2&"!"&ADDRESS(ROW(),COLUMN()))
To get the value in Sheet2!B2 instead, you can add 1 to COLUMN():
=INDIRECT(B2&"!"&ADDRESS(ROW(),COLUMN()+1))
Explanation:
INDIRECTreturns the value stored in a cell using some text formatted as a standard cell reference such as "A2", or "Sheet2!$A$2". Now we will try to generate that text automatically.
ADDRESS(row, col)converts cell and column numbers to a standard cell reference in text format. ADDRESS(1,1) returns "$A$1", and so on.
ROW()returns the number of the row the formula sits in, andCOLUMN()for the column number. Since the formula is in A2,ROW()will equal 2 andCOLUMN()will equal 1. These are just plain numbers, so we can add 1 to the column number to get B instead of A. We give those numbers toADDRESS(2,2)to get "$B$2".- We append this address to the sheet name we want that is in B2:
B2&!"&ADDRESS(ROW(),COLUMN()), not forgetting the exclamation mark between, to obtain the full cell reference we need: "Sheet2!$B$2" - Last step, we provide that text to
INDIRECT, which will go and fetch the value at that address.
answered Jul 8 '15 at 5:54
mtone
10.8k53659
If A2 in Sheet1 contains the formula, and B2 contains the sheet name "Sheet2", this will fetch the value in Sheet2!A2:
=INDIRECT(B2&"!"&ADDRESS(ROW(),COLUMN()))
To get the value in Sheet2!B2 instead, you can add 1 to COLUMN():
=INDIRECT(B2&"!"&ADDRESS(ROW(),COLUMN()+1))
Explanation:
INDIRECTreturns the value stored in a cell using some text formatted as a standard cell reference such as "A2", or "Sheet2!$A$2". Now we will try to generate that text automatically.
ADDRESS(row, col)converts cell and column numbers to a standard cell reference in text format. ADDRESS(1,1) returns "$A$1", and so on.
ROW()returns the number of the row the formula sits in, andCOLUMN()for the column number. Since the formula is in A2,ROW()will equal 2 andCOLUMN()will equal 1. These are just plain numbers, so we can add 1 to the column number to get B instead of A. We give those numbers toADDRESS(2,2)to get "$B$2".- We append this address to the sheet name we want that is in B2:
B2&!"&ADDRESS(ROW(),COLUMN()), not forgetting the exclamation mark between, to obtain the full cell reference we need: "Sheet2!$B$2" - Last step, we provide that text to
INDIRECT, which will go and fetch the value at that address.
answered Jul 8 '15 at 5:54
mtone
10.8k53659
edited Jul 8 '15 at 6:13
answered Jul 8 '15 at 5:54
mtone
10.8k53659
answered Jul 8 '15 at 5:54
mtone
10.8k53659
answered Jul 8 '15 at 5:54
mtone
10.8k53659
10.8k53659
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%2f937701%2fhow-to-match-cell-value-to-sheet-name-then-return-value-based-on-that%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
