Excel - totalling tabs in a summary sheet

Excel - totalling tabs in a summary sheet

Didn't find your answer?

Afternoon All,

I have 50+ tabs in excel with a summary sheet. The data in each tab is quite basis with a few columns of data and one of the columns has a figure. 

I want in column A of the summary sheet to contain the name of each tab. At the side of the tab name I want it to sum the total of a specific column in each tab (same column across all tabs). 

Other than lots of clicking, is there a clever way to do this quickly?

 

Thanks :)

Replies (13)

Please login or register to join the discussion.

Routemaster image
By tom123
13th Dec 2018 16:44

I would be interested in the answer, but don't know of a way to sum (say) cell A1 on all sheets.

I would worry about the data getting out of synch on some of the pages if things are added or moved etc.

Does the data really have to be on so many tabs?

Could it not instead be on the same tab, with another column at the start to identify whatever separates the pages - ie each region, department or whatever.

That way you could prepare easy summaries using pivot tables.

Pivot tables tend to be my goto whenever there are larger volumes - assuming of course you don't want to go down the database route.

Thanks (1)
Replying to tom123:
By mrme89
13th Dec 2018 17:08

Thanks Tom.

Yes, it has to be on multiple tabs.

The data is only across 4 columns and each tab is a specific charge code that will be uploaded to a customers account. The system is dictating the excel format.

The data itself is very simple, but lots of it.

The summary sheet is to, ahem, summarise, but also has links from the original data so we can quickly see any differences.

Thanks (0)
Replying to mrme89:
avatar
By WhiteRose
13th Dec 2018 18:13

If you could put all the data onto one tab it would be easier. Coding is the key to it all - add columns for the code(s) that refer to the specific customer or specific charge. Then, create pivot table(s) to extract the data that you want for each customer. Send just that information as a print.

Thanks (1)
avatar
By johnhemming
13th Dec 2018 18:11

You need to use the indirect function. It is probably easier to total each of the tabs say with the total going into the second line (so it is always in the same place). You can then use the indirect function to take the name of a tab and extract the relevant value.

I have an example of this on my MTD website under digital links. It shows how to use indirect to extract information from say old sage files or another computer system to bridge it into MTD.

The links to the spreadsheets (both excel and open office) are at the bottom of this page:
https://www.vat.direct/workflow/w/standardform/DigitalLinks

Thanks (1)
Replying to johnhemming:
By mrme89
14th Dec 2018 10:01

Thanks for that.

I think I need an INDIRECT with a SUMIF, but I can't get it to work for the life of me.

If I do get it working, I'll post it here.

Thanks (0)
Replying to mrme89:
avatar
By johnhemming
14th Dec 2018 19:09

mrme89 wrote:

I think I need an INDIRECT with a SUMIF, but I can't get it to work for the life of me.


I often find with spreadsheets that it is best to have a working worksheet that does all the calculations and then simply bring forward the figures that you want into a final worksheet.

Hence what I might do in this situation is to use INDIRECT to bring each original cell in each worksheet into a row in a working worksheet and then total that. Then I would bring the totals into another sheet.

What I like to have are cross checks which enable testing whether or not your formulae are correct. That ends up with balancing figures all over the place, but if you then take things to a summary sheet it can still look pretty as well as doing the instantaneous update.

One advantage of doing programming with databases for things like this is that you can build in the checks and balances that enable greater certainty that you are including all the right figures.

Spreadsheets are really good in that you can see all of the figures in a list hence finding mistakes is easier, but as soon as complicated formulae start being used it is harder to avoid mistakes (such as not including all of the source information in the totalling).

Thanks (1)
avatar
By bettybobbymeggie
13th Dec 2018 18:39

=SUM(Zz:aa!B2)

Where Zz is the first sheet in your range and aa the last.

**Edit**

Ignore that - I didn't read the question very well.

Thanks (1)
avatar
By bettybobbymeggie
14th Dec 2018 10:56

I think I managed it:

1. Create a name called SheetNames using the formula =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

2. In your summary tab, make column A a list of numbers representing the number of your tabs (eg. 1 to 50).

3. In column B enter the formula =INDEX(SheetNames,A1) where A1 contains the number 1. Copy that formula down - this should return a list of your tabs names.

3. In column C enter your sum or sumif. I did the most simple sum: =SUM(INDIRECT("'"&B1&"'!"&"b:b")) where B:B represents the column I am summing in each tab.

Does that work for you?

Thanks (2)
avatar
By paulwakefield1
14th Dec 2018 13:31

Power Query (Get and Transform) could also be used.

Assuming a Sumary sheet has already been created and the file saved, then code along the lines of:

let
Source = Excel.Workbook(File.Contents("C:\Users\Paul\Desktop\Book2.xlsx"), null, true),
#"Filtered Rows" = Table.SelectRows(Source, each ([Kind] = "Sheet")),
#"Removed Top Rows1" = Table.Skip(#"Filtered Rows",1),
#"Expanded Data" = Table.ExpandTableColumn(#"Removed Top Rows1", "Data", {"Column2"}, {"Data.Column2"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded Data",null,"0",Replacer.ReplaceValue,{"Data.Column2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Data.Column2", type number}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"Total", each List.Sum([Data.Column2]), type number}})
in
#"Grouped Rows"

This does require the file to be saved before refreshing so that new data is picked up. Perhaps a bigger constraint is that it assumes the data on each sheet is a similar structure - in my example, it is summing the second data column.

On the plus side, it will pick up new sheets automatically and does not require formulae.

Thanks (2)
Routemaster image
By tom123
14th Dec 2018 15:41

When I see replies like the ones above, I realise that my Excel skills (that I thought were above average) have somewhere to go :)

Thanks (1)
Replying to tom123:
avatar
By paulwakefield1
14th Dec 2018 17:15

It looks daunting but in reality you very rarely need to look at the language. The vast majority of it can be done with the interface which is pretty user friendly and reasonably self explanatory and it writes the language in the background.

The steps are just not so easy to post on a forum such as this so I posted the result which you wouldn't normally see! In reality there are just 7 steps in building the above; one would need to add only a few more to make it bullet proof.

But, if you need to analyse data from disparate sources or generally manipulate stuff, Power Query is well worth getting to know. One of its great benefits is that you can work through it step by step, go back and edit steps and see what is happening at each point; this makes it easy to achieve exactly what you want and to debug when it doesn't work!

Of course there are things which need a bit more in depth knowledge but MS are developing it pretty quickly and more and more can be done by point and click.

Evangelism over!

Thanks (2)
avatar
By FD4CAST FD4CAST
18th Jan 2019 20:20

Put this code in a module and run it - it should do what you want. PM me if you need any tweaking done to it:

Sub AddSummaryTotals()

Dim wksSummary As Worksheet
Dim wksTemp As Worksheet
Dim lngLastRow As Long

On Error GoTo Err_Handler

' Add Summary Sheet
Set wksSummary = ThisWorkbook.Sheets.Add(After:=Sheets(Sheets.Count))
With wksSummary
.Name = "Summary"
.Cells(1, "A").Value = "Sheet Name"
End With

For Each wksTemp In ThisWorkbook.Sheets
If wksTemp.Name <> wksSummary.Name Then
With wksSummary
lngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
.Cells(lngLastRow, "A").Value = wksTemp.Name
.Cells(lngLastRow, "B").FormulaR1C1 = "=SUM('" & wksTemp.Name & "'!C1)"
End With
End If
Next wksTemp

With wksSummary
.Columns("A:B").AutoFit
End With

Call MsgBox("Procedure Complete!", vbOKOnly + vbInformation, "Procedure Complete!")

Exit_Proc:
On Error Resume Next
Set wksSummary = Nothing
Set wksTemp = Nothing

Exit Sub

Err_Handler:
Stop
Resume

End Sub

Thanks (0)
avatar
By Alf
21st Jan 2019 09:18

Something i once found re "3D formulae":

"Usually references to a range of cells in a formula refer to a flat “rectangle” of cells on a single sheet. A 3D formula adds an extra dimension by extending the rectangle through several adjacent sheets.
To create a 3D Sum formula, click on the AutoSum button (or type =Sum) and then click on the tab of the first sheet to be included in the formula. SHIFT+Click on the last sheet to be included, then select the required cell or range of cells on this last sheet. The formula should look something like this: =SUM(Sheet2:Sheet3!A1:A3)
It refers to the range A1:A3 on all the sheets positioned between Sheet2 and Sheet3, including the end sheets themselves. Moving sheets into or out of the range will change the result."

You could either set the range to be (a part of) the relevant column or put a total formula at the top of each relevant column and then use a 3D formula to sum the totals

Thanks (0)