A pivot table to filter data from multiple tables, from multiple sheets?

[ADRSpec8]

Hello

This my first post. I'm using Excel 2016.

Background
My dad owns his own business and currently completes his accounts (Expenditure/Takings) using a generic ‘Accounts’ booklet he gets from the local stationery shop. Sometimes if an error in made in a calculation, this can carry through for several weeks/months which is time-consuming to correct. Therefore he has asked me to create an excel version of his accounts book. He wants it to have the same style and format to what he is used to.

Solution:
I have created a workbook which will contain 52 sheets called ‘Week 1’, ‘Week 2’… each containing 3 Tables - one for ‘Goods Purchased’, one for ‘Other Expenditure’ and a third Table for ‘Takings per day’.

These tables are named tables as ‘TableW1a’, ‘TableW1b’, ‘TableW1c’ respectively. The name of the table changes per sheet, on the sheet Week 2, the tables are ‘TableW2a’ etc…

This is really quite basic and does what exactly what it needs to. However due to the volume of data that this workbook will eventually contain, it would be useful to be able to do additional ‘analyses’ on the data, for example: to calculate the total expenditure for a given supplier based on the suppliers listed in the tables, or the average takings for a given day of the week.

Problem:
Essentially I want to create a new ‘summary’ worksheet that contains a pivot table – which consolidates the information from TableW1a, TableW2a, TableW3a…across all 52 sheets/weeks.

I have tried the following:

1. I tried to use the Pivot table wizard to consolidate multiple ranges, however this does not work well and although it can summarise some basic data, it is not easy to manipulate the filter criteria.

1. I have had a play around with using PowerPivot (my first time using this tool) and as any given series of tables contains duplicate values (such as Day of the week, or the supplier name), this option simply doesn’t work. Creating a data model seemed initially to be the way forward but this appears to be more limited than I realised – unless I’m not using it correctly?

1. I also tried creating in a new sheet, a table containing a static entry for each supplier, and a =SUMIF(TableW1a[Supplier],[@Supplier],TableW1a[Amount]) formula. However, this only works on specific tables. I tried to create a named range to multiple tables but this doesn’t appear to work in the formula (Maybe I did it wrong?). The alternative is multiple SUMIFs for each Table within the same formula but this obviously isn’t an easily manageable formula

Any ideas or suggestions would be greatly appreciated.

 

[Derek73]

Others may have better ideas, but I'd use VBA to consolidate the worksheets (I already have code to do that, below) into 1 master worksheet, pivot it, and delete the Master Table. That leaves you with only the pivot cache and the pivot table itself (pivot refresh won't work with the master table deleted...but it may be best to recreate it after any changes anyway). We'll always be limited to the million line limit, but it's highly improbable they have that many entries, even in a year. (4000 a day, 5 days = 20K rows, times 52 weeks = 1.04M lines).

The other problem it sounds like you are hitting is the field function expand and collapse working across multiple entries. For that, I'd suggest a helper column, such as Worksheet name (Week 1, for example) followed by day of the week. "Week 1 Monday", while still leaving the Monday column the master table in case you really want all Mondays.

Sub CombineSheets()
'Combines all worksheets in workbook into single sheet.
'Sheets need same format (Columns same, header same) or will look loopy
'Asks how many rows in header (label rows). 


Dim J As Integer, Ret_Type As Integer, x As Integer
Dim InputHeaders As Variant
Dim HeaderRows As Integer


    InputHeaders = InputBox("Column A cannot be blank. Data must be all the way to the left of the sheets. " & _
        "Sheets must have same headers (same layout). How many rows at the top are labels?", "Header Rows, Enter an Integer:", 1)
    If Not IsNumeric(InputHeaders) Then
            MsgBox ("You either hit cancel or didn't enter a number.")
            Exit Sub
        Else
            HeaderRows = CInt(Round(InputHeaders, 0))
            If CStr(HeaderRows) <> InputHeaders Then
                Ret_Type = MsgBox("Cute. Round it to " & HeaderRows & "?", vbOKCancel, "Not an Integer.")
                If Ret_Type = 2 Then Exit Sub
            End If
            If HeaderRows > 10 Then
            Ret_Type = MsgBox("Do you really have " & HeaderRows & " rows that are labels?", vbYesNoCancel + vbQuestion, "Really? Seems unrealistic.")
                Select Case Ret_Type
                    Case 6 'Yes
                         ' No Action
                    Case 7 'No
                        Exit Sub
                    Case 2 'Cancel
                        Exit Sub
                End Select
            End If
    End If


    On Error Resume Next
    Sheets(1).Select
    Worksheets.Add
    Sheets(2).Activate
    x = 1
    Do While x <= HeaderRows
        Sheets(2).Range("A" & x).EntireRow.Select
        Selection.Copy Destination:=Sheets(1).Range("A" & x)
        x = x + 1
    Loop
    For J = 2 To Sheets.Count
    Sheets(J).Activate
        Range("A1").Select
        Selection.CurrentRegion.Select
        Selection.Offset(HeaderRows, 0).Resize(Selection.Rows.Count - HeaderRows).Select
        Selection.Copy Destination:=Sheets(1).Range("A1048576").End(xlUp)(2)
    Next
    Sheets(1).Activate
'Commented out, not needed for this answer
     'Ret_Type = MsgBox(Sheets(1).Name & " is your combined sheet. Delete the other sheets?", vbOKCancel, "Result:")
     'If Ret_Type = 2 Then Exit Sub
     'Application.DisplayAlerts = False
     'Do While Sheets.Count > 1
      '   Sheets(2).Delete
     'Loop
    Application.DisplayAlerts = True


End Sub