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.
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.
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.
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
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).
=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.
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?
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.
When I see replies like the ones above, I realise that my Excel skills (that I thought were above average) have somewhere to go :)
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!
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
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