How to sum a cell across several worksheets using indirect cell reference The 2019 Stack...
Did the UK government pay "millions and millions of dollars" to try to snag Julian Assange?
How can I define good in a religion that claims no moral authority?
does high air pressure throw off wheel balance?
University's motivation for having tenure-track positions
Windows 10: How to Lock (not sleep) laptop on lid close?
What do you call a plan that's an alternative plan in case your initial plan fails?
Derivation tree not rendering
"... to apply for a visa" or "... and applied for a visa"?
Who or what is the being for whom Being is a question for Heidegger?
Working through the single responsibility principle (SRP) in Python when calls are expensive
Did God make two great lights or did He make the great light two?
Is there a writing software that you can sort scenes like slides in PowerPoint?
Does Parliament need to approve the new Brexit delay to 31 October 2019?
Can withdrawing asylum be illegal?
Mortgage adviser recommends a longer term than necessary combined with overpayments
Do warforged have souls?
Can the prologue be the backstory of your main character?
He got a vote 80% that of Emmanuel Macron’s
How did passengers keep warm on sail ships?
In horse breeding, what is the female equivalent of putting a horse out "to stud"?
What is special about square numbers here?
How to split my screen on my Macbook Air?
How can I protect witches in combat who wear limited clothing?
Cooking pasta in a water boiler
How to sum a cell across several worksheets using indirect cell reference
The 2019 Stack Overflow Developer Survey Results Are In
Announcing the arrival of Valued Associate #679: Cesar Manara
Planned maintenance scheduled April 17/18, 2019 at 00:00UTC (8:00pm US/Eastern)How to construct a cell reference from two numbers w/o using text()+indirect()?Excel- How do I reference a cell in one of two worksheets depending on what I am looking for?Taking an indirect reference in Excel and making it a direct referenceSumming a single cell Across Several WorksheetsExcel formula sum multiple rows/column once on cell has reached it's max number and continue to sumExcel - How to link Index/Matching formula with Indirect formula for dynamic column referenceUsing Excel Indirect with worksheet namesHow to keep a reference cell constant in a linked worksheet when a line is insertedUsing Address with Indirect to access cell on another sheetReference cell by identifier(not address) between sheets
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
Assume, I have Worksheets A-Z.
I want to SUM cell, C21, across several worksheets (e.g. A-Z).
I've tried this formula:
=SUM('A:Z'!C21)
However, now if I add rows in sheets A-Z, that moves the values of C21 to C24 the formula stays the same, i.e. the formula stays SUM('A:Z'!C21), whereas it should now be SUM('A:Z'!C24).
I believe I can solve this with an indirect cell reference. Something like:
=SUM('A:Z)'!(Indirect(CEll("address,C21))))
However, this returns an error ("There is a problem with this formula. Not trying to type a formula?....").
Does anybody know why? And is the right approach or is there a better approach?
microsoft-excel worksheet-function
edited yesterday
Rajesh S
4,4582725
asked yesterday
Karin Karin
1
New contributor
Karin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
Assume, I have Worksheets A-Z.
I want to SUM cell, C21, across several worksheets (e.g. A-Z).
I've tried this formula:
=SUM('A:Z'!C21)
However, now if I add rows in sheets A-Z, that moves the values of C21 to C24 the formula stays the same, i.e. the formula stays SUM('A:Z'!C21), whereas it should now be SUM('A:Z'!C24).
I believe I can solve this with an indirect cell reference. Something like:
=SUM('A:Z)'!(Indirect(CEll("address,C21))))
However, this returns an error ("There is a problem with this formula. Not trying to type a formula?....").
Does anybody know why? And is the right approach or is there a better approach?
microsoft-excel worksheet-function
edited yesterday
Rajesh S
4,4582725
asked yesterday
Karin Karin
1
New contributor
Karin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
If you have all the workbooks open and then make a change, Excel should automatically update the cells too. Indirect formulas won't work across multiple workbooks though.
– LPChip
yesterday
Thank you for your reply. I meant worksheets, not workbooks, perhaps you misread. I tried updating across all the sheets, yet the formula keeps referring to the old cell (c21) instead of the new (c24)
– Karin
yesterday
add a comment |
Assume, I have Worksheets A-Z.
I want to SUM cell, C21, across several worksheets (e.g. A-Z).
I've tried this formula:
=SUM('A:Z'!C21)
However, now if I add rows in sheets A-Z, that moves the values of C21 to C24 the formula stays the same, i.e. the formula stays SUM('A:Z'!C21), whereas it should now be SUM('A:Z'!C24).
I believe I can solve this with an indirect cell reference. Something like:
=SUM('A:Z)'!(Indirect(CEll("address,C21))))
However, this returns an error ("There is a problem with this formula. Not trying to type a formula?....").
Does anybody know why? And is the right approach or is there a better approach?
microsoft-excel worksheet-function
edited yesterday
Rajesh S
4,4582725
asked yesterday
Karin Karin
1
New contributor
Karin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Assume, I have Worksheets A-Z.
I want to SUM cell, C21, across several worksheets (e.g. A-Z).
I've tried this formula:
=SUM('A:Z'!C21)
However, now if I add rows in sheets A-Z, that moves the values of C21 to C24 the formula stays the same, i.e. the formula stays SUM('A:Z'!C21), whereas it should now be SUM('A:Z'!C24).
I believe I can solve this with an indirect cell reference. Something like:
=SUM('A:Z)'!(Indirect(CEll("address,C21))))
However, this returns an error ("There is a problem with this formula. Not trying to type a formula?....").
Does anybody know why? And is the right approach or is there a better approach?
microsoft-excel worksheet-function
microsoft-excel worksheet-function
edited yesterday
Rajesh S
4,4582725
asked yesterday
Karin Karin
1
New contributor
Karin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited yesterday
Rajesh S
4,4582725
asked yesterday
Karin Karin
1
New contributor
Karin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited yesterday
Rajesh S
4,4582725
edited yesterday
Rajesh S
4,4582725
edited yesterday
Rajesh S
4,4582725
4,4582725
asked yesterday
Karin Karin
1
New contributor
Karin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked yesterday
Karin Karin
1
asked yesterday
Karin Karin
1
1
New contributor
Karin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Karin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Karin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
If you have all the workbooks open and then make a change, Excel should automatically update the cells too. Indirect formulas won't work across multiple workbooks though.
– LPChip
yesterday
Thank you for your reply. I meant worksheets, not workbooks, perhaps you misread. I tried updating across all the sheets, yet the formula keeps referring to the old cell (c21) instead of the new (c24)
– Karin
yesterday
add a comment |
If you have all the workbooks open and then make a change, Excel should automatically update the cells too. Indirect formulas won't work across multiple workbooks though.
– LPChip
yesterday
Thank you for your reply. I meant worksheets, not workbooks, perhaps you misread. I tried updating across all the sheets, yet the formula keeps referring to the old cell (c21) instead of the new (c24)
– Karin
yesterday
If you have all the workbooks open and then make a change, Excel should automatically update the cells too. Indirect formulas won't work across multiple workbooks though.
– LPChip
yesterday
If you have all the workbooks open and then make a change, Excel should automatically update the cells too. Indirect formulas won't work across multiple workbooks though.
– LPChip
yesterday
Thank you for your reply. I meant worksheets, not workbooks, perhaps you misread. I tried updating across all the sheets, yet the formula keeps referring to the old cell (c21) instead of the new (c24)
– Karin
yesterday
Thank you for your reply. I meant worksheets, not workbooks, perhaps you misread. I tried updating across all the sheets, yet the formula keeps referring to the old cell (c21) instead of the new (c24)
– Karin
yesterday
add a comment |
1 Answer
1
active
oldest
votes
This simple Formula can solve your issue:
=SUM(B:Z!C21)
Or, if you want to use INDIRECT with SUM
then do the following:
Anywhere in Sheet, enter Sheet name and
put one heading like in my sample data
below is, SheetName.
Select Entire Range including Header, and
from Formula Tab, click Create from
selection and select Top Row option,
finish with Ok.
Enter this formula in any blank Cell
=SUMPRODUCT(SUM(INDIRECT("'"&SheetName&"'!"&"C21:C21")))
Adjust Sheet Name, Cell references and Range Name in Formula as needed.
answered yesterday
Rajesh SRajesh S
4,4582725
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
});
}
});
Karin is a new contributor. Be nice, and check out our Code of Conduct.
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%2f1424604%2fhow-to-sum-a-cell-across-several-worksheets-using-indirect-cell-reference%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
This simple Formula can solve your issue:
=SUM(B:Z!C21)
Or, if you want to use INDIRECT with SUM
then do the following:
Anywhere in Sheet, enter Sheet name and
put one heading like in my sample data
below is, SheetName.
Select Entire Range including Header, and
from Formula Tab, click Create from
selection and select Top Row option,
finish with Ok.
Enter this formula in any blank Cell
=SUMPRODUCT(SUM(INDIRECT("'"&SheetName&"'!"&"C21:C21")))
Adjust Sheet Name, Cell references and Range Name in Formula as needed.
answered yesterday
Rajesh SRajesh S
4,4582725
add a comment |
This simple Formula can solve your issue:
=SUM(B:Z!C21)
Or, if you want to use INDIRECT with SUM
then do the following:
Anywhere in Sheet, enter Sheet name and
put one heading like in my sample data
below is, SheetName.
Select Entire Range including Header, and
from Formula Tab, click Create from
selection and select Top Row option,
finish with Ok.
Enter this formula in any blank Cell
=SUMPRODUCT(SUM(INDIRECT("'"&SheetName&"'!"&"C21:C21")))
Adjust Sheet Name, Cell references and Range Name in Formula as needed.
answered yesterday
Rajesh SRajesh S
4,4582725
add a comment |
This simple Formula can solve your issue:
=SUM(B:Z!C21)
Or, if you want to use INDIRECT with SUM
then do the following:
Anywhere in Sheet, enter Sheet name and
put one heading like in my sample data
below is, SheetName.
Select Entire Range including Header, and
from Formula Tab, click Create from
selection and select Top Row option,
finish with Ok.
Enter this formula in any blank Cell
=SUMPRODUCT(SUM(INDIRECT("'"&SheetName&"'!"&"C21:C21")))
Adjust Sheet Name, Cell references and Range Name in Formula as needed.
answered yesterday
Rajesh SRajesh S
4,4582725
This simple Formula can solve your issue:
=SUM(B:Z!C21)
Or, if you want to use INDIRECT with SUM
then do the following:
Anywhere in Sheet, enter Sheet name and
put one heading like in my sample data
below is, SheetName.
Select Entire Range including Header, and
from Formula Tab, click Create from
selection and select Top Row option,
finish with Ok.
Enter this formula in any blank Cell
=SUMPRODUCT(SUM(INDIRECT("'"&SheetName&"'!"&"C21:C21")))
Adjust Sheet Name, Cell references and Range Name in Formula as needed.
answered yesterday
Rajesh SRajesh S
4,4582725
edited yesterday
answered yesterday
Rajesh SRajesh S
4,4582725
answered yesterday
Rajesh SRajesh S
4,4582725
answered yesterday
Rajesh SRajesh S
4,4582725
4,4582725
add a comment |
add a comment |
Karin is a new contributor. Be nice, and check out our Code of Conduct.
Karin is a new contributor. Be nice, and check out our Code of Conduct.
Karin is a new contributor. Be nice, and check out our Code of Conduct.
Karin is a new contributor. Be nice, and check out our Code of Conduct.
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%2f1424604%2fhow-to-sum-a-cell-across-several-worksheets-using-indirect-cell-reference%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
If you have all the workbooks open and then make a change, Excel should automatically update the cells too. Indirect formulas won't work across multiple workbooks though.
– LPChip
yesterday
Thank you for your reply. I meant worksheets, not workbooks, perhaps you misread. I tried updating across all the sheets, yet the formula keeps referring to the old cell (c21) instead of the new (c24)
– Karin
yesterday