Totaling values of a single cell from ALL existing worksheets.

I have a workbook that has 20-30 Tabs of various names, all identically formatted.  Is there a way to total cells from each worksheet without an enormous statement like =SUM(HomeDepot!C7+Sears!C7+WalMart!C7...) etc.?
LVL 1
Bill GoldenExecutive Managing MemberAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
even each sheet's formatting is the same u need to tell Excel which cells to add into ur formula.

so u need to hav a series of sum formula, like

=SUM(HomeDepot!C7+Sears!C7+WalMart!C7...)
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
if not we gonna to do it via VBA in workbook_open event to add those values together.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
for my 2nd suggestion, you can try:

Private Sub Workbook_Open()
    For Each ws In Worksheets
        If ws.Name <> Worksheets(1).Name Then
            v = v + ws.Cells(7, "C")
        End If
    Next
    Worksheets(1).Cells(3, "C") = v
End Sub

Open in new window

28699456.xlsm
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Roy CoxGroup Finance ManagerCommented:
This trick can be adapted for your needs.
If you have data on several sheets and need to create a summary then this example demonstrates a simple way to  create a summary sheet of the data from those sheets.
Note: The layout of the data must be exactly the same on each sheet, i. e corresponding amounts should always be in the same cell on each sheet.
For example a chain of shops has a sheet for each shop that records sales.. These sheets should be collected into one workbook, that in addition has a sheet with the same layout to hold the summary. You could use a formula like:
 =Sheet1!B2 +Sheet2!B2 + Sheet3!B8
However, this formula will need updating whenever a sheet is added or removed. My example demonstrates a simple way to summarise the data that will not involve having to change formulas if you change the number of data sheets. To make this trick work you need to add two sheets, one between the summary sheet and the the first data sheet and the other sheet should be to the right of the last data sheet. In the example I have named them Top and Bottom. In the summary sheet you add formulas to sum the corresponding data like:=SUM(Top:Bottom!B2)
In the formula Top & Bottom are the outside sheet names. So this formula will add up the amounts on each sheet between these sheets that is in B2. If you use this method to record monthly data then you can add sheets as required. Providing that you use the same layout and place the new sheet between “Top” & “Bottom” then the data will update without any changes to the formula. By moving a sheet outside of the Top & Bottom tabs, that sheet's data is no longer included in the Summary sheet.
In Excel you can move the position of a sheet by simply clicking on the relevant Worksheet tab and dragging it to a different position. This makes this trick even more useful because you can see what would happen to the summary if you move a data sheet from between between the Top & Bottom tabs. This data is then removed from the totals, so in the simple example you could see the effect of closing one of the group’s branches.
I have added a macro that adds a sheet in the specified position between Top  & Bottom. This sheet is based on a hidden Template sheet.in the workbook. The user is prompted by a Excel VBA MessageBox to provide a new name for the record sheet that is added.
royUK-Summarise-Data-Tip--1-.zip

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You have two options.....

Assuming you have one Summary Sheet and some other sheets with data which you want to summarize on Summary sheet.

In my example, I have assumed your first sheet is Summary Sheet and 17 other sheets named Sheet1, Sheet2....Sheet18. These names could be different as per your actual workbook and should be changed in the proposed formulas accordingly.

Method1

In your summary sheet, in the formula cell, try this...

=SUM(Sheet2:Sheet18!C7)

Open in new window


If the other formula doesn't work for you, follow these steps.....

1) In the formula cell type =SUM(
2) Now click on the first data sheet tab (in this example Sheet2 tab).
3) Hold down the Shift key and click on your last data sheet tab (in this example Sheet18).
4) Click on Cell C7.
5) Close the closing bracket of the formula and press Enter.


Method 2

In any column of your choice list all the Sheet Names, in the proposed formula, I have listed the Sheet Names in col. P starting from P2:P18.

Now in the formula cell try this.....

=SUMPRODUCT(SUM(INDIRECT("'"&P2:P18&"'!C7")))

Open in new window


Both the methods will give you the sum of cell C7 of each data sheet.

For details refer to the attached file.

Does this help?
Sum-Across-Multiple-Sheets.xlsx
Bill GoldenExecutive Managing MemberAuthor Commented:
I hope to test the various solutions provided during the next two days.
Martin LissOlder than dirtCommented:
I've requested that this question be closed as follows:

Accepted answer: 250 points for Roy_Cox's comment #a40894622
Assisted answer: 250 points for sktneer's comment #a40894644

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Bill GoldenExecutive Managing MemberAuthor Commented:
I wasn't able to do exactly what I wanted, but the various advice and solutions helped me shorten the process.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.