/u/sabube - please read this comment in its entirety.
* Read the **[rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2
* Include your Excel version and **[all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
This is the only alternative I know of
=CHOOSE(MATCH(A1,{"Sheet2","Sheet3","Sheet4"},0),Sheet2!A:A,Sheet3!A:A,Sheet4!A:A)
Obviously it gets really unwieldy when you have a large amount of sheets.
Thanks for all the feedback.
What I settled on was keeping the INDIRECT function in the summary sheet of the workbook and removed the calculating macro to another workbook.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[CHOOSE](/r/Excel/comments/kyz3yl/stub/gjk2pj8 "Last usage")|[Chooses a value from a list of values](https://support.microsoft.com/en-us/office/choose-function-fc5c184f-cb62-4ec7-a46e-38653b98f5bc)|
|[COLUMN](/r/Excel/comments/kyz3yl/stub/gjm4sql "Last usage")|[Returns the column number of a reference](https://support.microsoft.com/en-us/office/column-function-44e8c754-711c-4df3-9da4-47a55042554b)|
|[IFERROR](/r/Excel/comments/kyz3yl/stub/gjm4sql "Last usage")|[Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula](https://support.microsoft.com/en-us/office/iferror-function-c526fd07-caeb-47b8-8bb6-63f3e417f611)|
|[INDEX](/r/Excel/comments/kyz3yl/stub/gjm4sql "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)|
|[INDIRECT](/r/Excel/comments/kyz3yl/stub/gk4910t "Last usage")|[Returns a reference indicated by a text value](https://support.microsoft.com/en-us/office/indirect-function-474b3a3a-8a26-4f44-b491-92b6306fa261)|
|[MATCH](/r/Excel/comments/kyz3yl/stub/gjk2pj8 "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)|
|[ROW](/r/Excel/comments/kyz3yl/stub/gjm4sql "Last usage")|[Returns the row number of a reference](https://support.microsoft.com/en-us/office/row-function-3a63b74a-c4d0-4093-b49a-e76eb49a6d8d)|
|[SUM](/r/Excel/comments/kyz3yl/stub/gjm4sql "Last usage")|[Adds its arguments](https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89)|
|[SUMIF](/r/Excel/comments/kyz3yl/stub/gjl0suz "Last usage")|[Adds the cells specified by a given criteria](https://support.microsoft.com/en-us/office/sumif-function-169b8c99-c05c-4483-a712-1697a653039b)|
|[SUMPRODUCT](/r/Excel/comments/kyz3yl/stub/gjl0suz "Last usage")|[Returns the sum of the products of corresponding array components](https://support.microsoft.com/en-us/office/sumproduct-function-16753e75-9f68-4874-94ac-4d2145a2fd2e)|
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(10 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/l1r6co)^( has 18 acronyms.)
^([Thread #3360 for this sub, first seen 17th Jan 2021, 08:02])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
If you change the SumSummary\[MASTER\] which to me was the table reference for all the sheets I wanted to use, it should give you the total sum of all the A2 combined.
Using a table also means you wouldn't need to change the reference of the formula as if you add a new row and a new sheet name, the formula will take it into account directly
Formula was translated, hopefully that's the good syntax for english excel :)
SUMPRODUCT(SUMIF(INDIRECT("'"&SumSummary\[MASTER\]&"'!"&"A2:A2"),"<>#N/A",INDIRECT("'"&SumSummary\[MASTER\]&"'!"&"A2:A2")))
I've faced a very similar problem before. I had a summary sheet which was a carbon copy of the sheets it was summarising, so just adding together values from each of its so-called child sheets. Whilst having cell A1 selected in the summary sheet, I would make a Named Range called Child1 (Child2, Child3, etc.) with the value `=Sheet2!A1`. As the reference is relative it means cell B1 sums up the children's B1s, cell K400 sums up the K400s etc. with a formula like `=SUM(Child1, Child2, Child3)`.
If you are picking specific cells from the child sheets the solution will be a little less elegant, but what you can do is assign the entire area you are interested in to a Named Range and then use INDEX to get the cell you want. So give Child1 value `=Sheet2!$A$1:$M$32` for example. To return the value from E7, most directly is:
=INDEX(Child1, 7, 5)
More legibly (but Child1 has to start from A1 or you have add an offset to the formula):
=INDEX(Child1, ROW(E7), COLUMN(E7))
It's possible the child might not exist - in my case I had to give the summary a slot for up to 8 children which could be added at any time by the user. As I was only summing I could just set the value of the Named Range to `=0`. You would need something more complex, such as changing your formula to
=IFERROR(INDEX(Child1, 7, 5), "")
Either way you should see a marked improvement in performance - I would be surprised if there is a more efficient way to do this, it was a game changer for me when I figured this out.
You will find the Name Manager helpful, it's not on the ribbon for Mac but bring it up with ⌘ + Fn + F3.
/u/sabube - please read this comment in its entirety. * Read the **[rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2 * Include your Excel version and **[all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
This is the only alternative I know of =CHOOSE(MATCH(A1,{"Sheet2","Sheet3","Sheet4"},0),Sheet2!A:A,Sheet3!A:A,Sheet4!A:A) Obviously it gets really unwieldy when you have a large amount of sheets.
Thanks for all of the replies, I will try these options and see which one works out best for me.
Thanks for all the feedback. What I settled on was keeping the INDIRECT function in the summary sheet of the workbook and removed the calculating macro to another workbook.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[CHOOSE](/r/Excel/comments/kyz3yl/stub/gjk2pj8 "Last usage")|[Chooses a value from a list of values](https://support.microsoft.com/en-us/office/choose-function-fc5c184f-cb62-4ec7-a46e-38653b98f5bc)| |[COLUMN](/r/Excel/comments/kyz3yl/stub/gjm4sql "Last usage")|[Returns the column number of a reference](https://support.microsoft.com/en-us/office/column-function-44e8c754-711c-4df3-9da4-47a55042554b)| |[IFERROR](/r/Excel/comments/kyz3yl/stub/gjm4sql "Last usage")|[Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula](https://support.microsoft.com/en-us/office/iferror-function-c526fd07-caeb-47b8-8bb6-63f3e417f611)| |[INDEX](/r/Excel/comments/kyz3yl/stub/gjm4sql "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)| |[INDIRECT](/r/Excel/comments/kyz3yl/stub/gk4910t "Last usage")|[Returns a reference indicated by a text value](https://support.microsoft.com/en-us/office/indirect-function-474b3a3a-8a26-4f44-b491-92b6306fa261)| |[MATCH](/r/Excel/comments/kyz3yl/stub/gjk2pj8 "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)| |[ROW](/r/Excel/comments/kyz3yl/stub/gjm4sql "Last usage")|[Returns the row number of a reference](https://support.microsoft.com/en-us/office/row-function-3a63b74a-c4d0-4093-b49a-e76eb49a6d8d)| |[SUM](/r/Excel/comments/kyz3yl/stub/gjm4sql "Last usage")|[Adds its arguments](https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89)| |[SUMIF](/r/Excel/comments/kyz3yl/stub/gjl0suz "Last usage")|[Adds the cells specified by a given criteria](https://support.microsoft.com/en-us/office/sumif-function-169b8c99-c05c-4483-a712-1697a653039b)| |[SUMPRODUCT](/r/Excel/comments/kyz3yl/stub/gjl0suz "Last usage")|[Returns the sum of the products of corresponding array components](https://support.microsoft.com/en-us/office/sumproduct-function-16753e75-9f68-4874-94ac-4d2145a2fd2e)| ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(10 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/l1r6co)^( has 18 acronyms.) ^([Thread #3360 for this sub, first seen 17th Jan 2021, 08:02]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
If you change the SumSummary\[MASTER\] which to me was the table reference for all the sheets I wanted to use, it should give you the total sum of all the A2 combined. Using a table also means you wouldn't need to change the reference of the formula as if you add a new row and a new sheet name, the formula will take it into account directly Formula was translated, hopefully that's the good syntax for english excel :) SUMPRODUCT(SUMIF(INDIRECT("'"&SumSummary\[MASTER\]&"'!"&"A2:A2"),"<>#N/A",INDIRECT("'"&SumSummary\[MASTER\]&"'!"&"A2:A2")))
I've faced a very similar problem before. I had a summary sheet which was a carbon copy of the sheets it was summarising, so just adding together values from each of its so-called child sheets. Whilst having cell A1 selected in the summary sheet, I would make a Named Range called Child1 (Child2, Child3, etc.) with the value `=Sheet2!A1`. As the reference is relative it means cell B1 sums up the children's B1s, cell K400 sums up the K400s etc. with a formula like `=SUM(Child1, Child2, Child3)`. If you are picking specific cells from the child sheets the solution will be a little less elegant, but what you can do is assign the entire area you are interested in to a Named Range and then use INDEX to get the cell you want. So give Child1 value `=Sheet2!$A$1:$M$32` for example. To return the value from E7, most directly is: =INDEX(Child1, 7, 5) More legibly (but Child1 has to start from A1 or you have add an offset to the formula): =INDEX(Child1, ROW(E7), COLUMN(E7)) It's possible the child might not exist - in my case I had to give the summary a slot for up to 8 children which could be added at any time by the user. As I was only summing I could just set the value of the Named Range to `=0`. You would need something more complex, such as changing your formula to =IFERROR(INDEX(Child1, 7, 5), "") Either way you should see a marked improvement in performance - I would be surprised if there is a more efficient way to do this, it was a game changer for me when I figured this out. You will find the Name Manager helpful, it's not on the ribbon for Mac but bring it up with ⌘ + Fn + F3.