Excel: Reference row indicated by cell
I have a sheet where each row is a month and columns are monthly numbers.
I made a separate sheet for each month where the numbers from each month can be pulled together and added up.
To do this, I've made a template for January and used references like: ='2019'!A8
I went through each month and changed the row number being referenced. ( ='2019'!A8 would change to ='2019'!A9 for the next month.)
Is it possible to pull the number for the reference from a different cell?
Could I have somehow referenced a cell which was just the number and used it for ='2019'!A#` Rather than change all my references by hand?
excel excel-formula
edited Nov 24 '18 at 17:33
Pragmateek
9,26685488
asked Nov 24 '18 at 14:42
mrbobdobolinamrbobdobolina
63
add a comment |
I have a sheet where each row is a month and columns are monthly numbers.
I made a separate sheet for each month where the numbers from each month can be pulled together and added up.
To do this, I've made a template for January and used references like: ='2019'!A8
I went through each month and changed the row number being referenced. ( ='2019'!A8 would change to ='2019'!A9 for the next month.)
Is it possible to pull the number for the reference from a different cell?
Could I have somehow referenced a cell which was just the number and used it for ='2019'!A#` Rather than change all my references by hand?
excel excel-formula
edited Nov 24 '18 at 17:33
Pragmateek
9,26685488
asked Nov 24 '18 at 14:42
mrbobdobolinamrbobdobolina
63
You might also consider putting all your data in one sheet and using a pivot table in another sheet to analyze it. I'm not clear on your design, but my experience is that designs like this with data spread across multiple sheets is hard to maintain and error-prone.
– Doug Glancy
Nov 24 '18 at 17:51
In my opinion, best solution is the one which is easy to maintain, update or change. I would not go for indirect references, but rather update my file structure to be more simple that does not require such formulas
– usmanhaq
Nov 25 '18 at 11:48
add a comment |
I have a sheet where each row is a month and columns are monthly numbers.
I made a separate sheet for each month where the numbers from each month can be pulled together and added up.
To do this, I've made a template for January and used references like: ='2019'!A8
I went through each month and changed the row number being referenced. ( ='2019'!A8 would change to ='2019'!A9 for the next month.)
Is it possible to pull the number for the reference from a different cell?
Could I have somehow referenced a cell which was just the number and used it for ='2019'!A#` Rather than change all my references by hand?
excel excel-formula
edited Nov 24 '18 at 17:33
Pragmateek
9,26685488
asked Nov 24 '18 at 14:42
mrbobdobolinamrbobdobolina
63
I have a sheet where each row is a month and columns are monthly numbers.
I made a separate sheet for each month where the numbers from each month can be pulled together and added up.
To do this, I've made a template for January and used references like: ='2019'!A8
I went through each month and changed the row number being referenced. ( ='2019'!A8 would change to ='2019'!A9 for the next month.)
Is it possible to pull the number for the reference from a different cell?
Could I have somehow referenced a cell which was just the number and used it for ='2019'!A#` Rather than change all my references by hand?
excel excel-formula
excel excel-formula
edited Nov 24 '18 at 17:33
Pragmateek
9,26685488
asked Nov 24 '18 at 14:42
mrbobdobolinamrbobdobolina
63
edited Nov 24 '18 at 17:33
Pragmateek
9,26685488
asked Nov 24 '18 at 14:42
mrbobdobolinamrbobdobolina
63
edited Nov 24 '18 at 17:33
Pragmateek
9,26685488
edited Nov 24 '18 at 17:33
Pragmateek
9,26685488
edited Nov 24 '18 at 17:33
Pragmateek
9,26685488
9,26685488
asked Nov 24 '18 at 14:42
mrbobdobolinamrbobdobolina
63
asked Nov 24 '18 at 14:42
mrbobdobolinamrbobdobolina
63
asked Nov 24 '18 at 14:42
mrbobdobolinamrbobdobolina
63
63
You might also consider putting all your data in one sheet and using a pivot table in another sheet to analyze it. I'm not clear on your design, but my experience is that designs like this with data spread across multiple sheets is hard to maintain and error-prone.
– Doug Glancy
Nov 24 '18 at 17:51
In my opinion, best solution is the one which is easy to maintain, update or change. I would not go for indirect references, but rather update my file structure to be more simple that does not require such formulas
– usmanhaq
Nov 25 '18 at 11:48
add a comment |
You might also consider putting all your data in one sheet and using a pivot table in another sheet to analyze it. I'm not clear on your design, but my experience is that designs like this with data spread across multiple sheets is hard to maintain and error-prone.
– Doug Glancy
Nov 24 '18 at 17:51
In my opinion, best solution is the one which is easy to maintain, update or change. I would not go for indirect references, but rather update my file structure to be more simple that does not require such formulas
– usmanhaq
Nov 25 '18 at 11:48
You might also consider putting all your data in one sheet and using a pivot table in another sheet to analyze it. I'm not clear on your design, but my experience is that designs like this with data spread across multiple sheets is hard to maintain and error-prone.
– Doug Glancy
Nov 24 '18 at 17:51
You might also consider putting all your data in one sheet and using a pivot table in another sheet to analyze it. I'm not clear on your design, but my experience is that designs like this with data spread across multiple sheets is hard to maintain and error-prone.
– Doug Glancy
Nov 24 '18 at 17:51
In my opinion, best solution is the one which is easy to maintain, update or change. I would not go for indirect references, but rather update my file structure to be more simple that does not require such formulas
– usmanhaq
Nov 25 '18 at 11:48
In my opinion, best solution is the one which is easy to maintain, update or change. I would not go for indirect references, but rather update my file structure to be more simple that does not require such formulas
– usmanhaq
Nov 25 '18 at 11:48
add a comment |
2 Answers
2
active
oldest
votes
With a month indicator in each monthly sheet (say in D1 and 03 for March) then with all the monthly sheets grouped together my present understanding of what it is you want should be achieved with:
=INDIRECT("2019!A"&$D1+7)
(Don't forget to ungroup.)
answered Nov 24 '18 at 15:19
pnutspnuts
48.7k76299
add a comment |
I suppose your data look like:
Month Sales
January 500
February 1000
March 1500
... ...
Moreover somewhere on your monthly sheets I suppose the month will be written in a cell, say A1.
Then you could simply use the same VLOOKUP on all these sheets:
=VLOOKUP(A1;SALES_DATA;2;FALSE)
And if your monthly sheets name contain the month name in some way, you could even do something like:
=VLOOKUP(MID(CELL("filename";A1);FIND("]";CELL("filename";A1))+1;255);SALES_DATA;2;FALSE)
CELL("filename";A1) retrieves the whole sheet path like C:Users...Desktop[Sales.xlsm]March".
answered Nov 24 '18 at 17:27
PragmateekPragmateek
9,26685488
add a comment |
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%2f53459284%2fexcel-reference-row-indicated-by-cell%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
With a month indicator in each monthly sheet (say in D1 and 03 for March) then with all the monthly sheets grouped together my present understanding of what it is you want should be achieved with:
=INDIRECT("2019!A"&$D1+7)
(Don't forget to ungroup.)
answered Nov 24 '18 at 15:19
pnutspnuts
48.7k76299
add a comment |
With a month indicator in each monthly sheet (say in D1 and 03 for March) then with all the monthly sheets grouped together my present understanding of what it is you want should be achieved with:
=INDIRECT("2019!A"&$D1+7)
(Don't forget to ungroup.)
answered Nov 24 '18 at 15:19
pnutspnuts
48.7k76299
add a comment |
With a month indicator in each monthly sheet (say in D1 and 03 for March) then with all the monthly sheets grouped together my present understanding of what it is you want should be achieved with:
=INDIRECT("2019!A"&$D1+7)
(Don't forget to ungroup.)
answered Nov 24 '18 at 15:19
pnutspnuts
48.7k76299
With a month indicator in each monthly sheet (say in D1 and 03 for March) then with all the monthly sheets grouped together my present understanding of what it is you want should be achieved with:
=INDIRECT("2019!A"&$D1+7)
(Don't forget to ungroup.)
answered Nov 24 '18 at 15:19
pnutspnuts
48.7k76299
answered Nov 24 '18 at 15:19
pnutspnuts
48.7k76299
answered Nov 24 '18 at 15:19
pnutspnuts
48.7k76299
answered Nov 24 '18 at 15:19
pnutspnuts
48.7k76299
48.7k76299
add a comment |
add a comment |
I suppose your data look like:
Month Sales
January 500
February 1000
March 1500
... ...
Moreover somewhere on your monthly sheets I suppose the month will be written in a cell, say A1.
Then you could simply use the same VLOOKUP on all these sheets:
=VLOOKUP(A1;SALES_DATA;2;FALSE)
And if your monthly sheets name contain the month name in some way, you could even do something like:
=VLOOKUP(MID(CELL("filename";A1);FIND("]";CELL("filename";A1))+1;255);SALES_DATA;2;FALSE)
CELL("filename";A1) retrieves the whole sheet path like C:Users...Desktop[Sales.xlsm]March".
answered Nov 24 '18 at 17:27
PragmateekPragmateek
9,26685488
add a comment |
I suppose your data look like:
Month Sales
January 500
February 1000
March 1500
... ...
Moreover somewhere on your monthly sheets I suppose the month will be written in a cell, say A1.
Then you could simply use the same VLOOKUP on all these sheets:
=VLOOKUP(A1;SALES_DATA;2;FALSE)
And if your monthly sheets name contain the month name in some way, you could even do something like:
=VLOOKUP(MID(CELL("filename";A1);FIND("]";CELL("filename";A1))+1;255);SALES_DATA;2;FALSE)
CELL("filename";A1) retrieves the whole sheet path like C:Users...Desktop[Sales.xlsm]March".
answered Nov 24 '18 at 17:27
PragmateekPragmateek
9,26685488
add a comment |
I suppose your data look like:
Month Sales
January 500
February 1000
March 1500
... ...
Moreover somewhere on your monthly sheets I suppose the month will be written in a cell, say A1.
Then you could simply use the same VLOOKUP on all these sheets:
=VLOOKUP(A1;SALES_DATA;2;FALSE)
And if your monthly sheets name contain the month name in some way, you could even do something like:
=VLOOKUP(MID(CELL("filename";A1);FIND("]";CELL("filename";A1))+1;255);SALES_DATA;2;FALSE)
CELL("filename";A1) retrieves the whole sheet path like C:Users...Desktop[Sales.xlsm]March".
answered Nov 24 '18 at 17:27
PragmateekPragmateek
9,26685488
I suppose your data look like:
Month Sales
January 500
February 1000
March 1500
... ...
Moreover somewhere on your monthly sheets I suppose the month will be written in a cell, say A1.
Then you could simply use the same VLOOKUP on all these sheets:
=VLOOKUP(A1;SALES_DATA;2;FALSE)
And if your monthly sheets name contain the month name in some way, you could even do something like:
=VLOOKUP(MID(CELL("filename";A1);FIND("]";CELL("filename";A1))+1;255);SALES_DATA;2;FALSE)
CELL("filename";A1) retrieves the whole sheet path like C:Users...Desktop[Sales.xlsm]March".
answered Nov 24 '18 at 17:27
PragmateekPragmateek
9,26685488
answered Nov 24 '18 at 17:27
PragmateekPragmateek
9,26685488
answered Nov 24 '18 at 17:27
PragmateekPragmateek
9,26685488
answered Nov 24 '18 at 17:27
PragmateekPragmateek
9,26685488
9,26685488
add a comment |
add a comment |
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%2f53459284%2fexcel-reference-row-indicated-by-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
You might also consider putting all your data in one sheet and using a pivot table in another sheet to analyze it. I'm not clear on your design, but my experience is that designs like this with data spread across multiple sheets is hard to maintain and error-prone.
– Doug Glancy
Nov 24 '18 at 17:51
In my opinion, best solution is the one which is easy to maintain, update or change. I would not go for indirect references, but rather update my file structure to be more simple that does not require such formulas
– usmanhaq
Nov 25 '18 at 11:48