Combining sum result from multiple tables

[tomsov]

Hello,
I'm struggling with a combined table result issue as follows and I'm hoping someone can help.

I've got 5 seperate sheets, all with named tables that are exactly the same format - the tables refer to customer orders that contain the same data - so order live/dead, order number, part number, qty, description, cost etc.
The tables are large so hold a lot of data.
What I need to do on my index page is have an input cell that once I put a part number into it, the cells (or table) to the side will show the sum result of every instance of that part number on each of the 5 tables. So if I enter the part number the routine needs to look at all of the tables on all of the sheets and result in showing that on sheet 1 the part number has a total quantity of 500 on order, sheet 2 has 200 on order etc. The part number will be repeated on each sheet depending on how many is ordered on each order number so it will appear over many lines - so the total has to be summed up. Oh also, I need to check the first column for a 1 (live) or 0 (dead) so that only live results are given.
The purpose of this is so that I can type the part number into my index sheet and excel will return the total qty of all the instances of that part number for sheet 1, (result to cell A1), then again for sheet 2 (result to cell B1) etc (be better to output it to a table of course though), so the results will appear next to the customer name to show how many of that part number remains outstanding.

I've tried using alt+d then p for the pivot table that'll use multiple table inputs but it errors saying something about cant use merged cells (which there isn't any). Using pivot table relationships causes an out of memory error saying that excel needs to run in 64bit mode (it is already i think).

Sorry its a complicated one, any ideas? (I can redesign things as necessary to accommodate the solution but the tables have to remain on seperate sheets for ease of employee usage)

Thank you.

 

[maabadi]

Please Upload Example file & Desired results with XL2BB ADDIN(Preferable) or upload at free hosting Site e.g. www.dropbox.com , GoogleDrive or OneDrive & insert Link here.

 

[tomsov]

I've managed to find this solution that seems to work rather well. It uses the text that is in cell D17 in the worksheet "Index" to automatically alter a pivot table (called "PartNumberPIVOT") to filter and show the sum of the parts of the same number - in the same way as if you were to use the drop down arrows in the pivot table filter, but using cell D17 as the value.

Here's the code:

If Intersect(Target, Range("D17")) Is Nothing Then Exit Sub
Dim pt As PivotTable
Dim Field As PivotField
Set pt = Worksheets("Index").PivotTables("PartNumberPIVOT")
Set Field = pt.PivotFields("Part Number")
With ActiveSheet.PivotTables("PartNumberPIVOT").PivotFields("Part Number")
.ClearAllFilters
.PivotFilters.Add Type:=xlCaptionEquals, Value1:=ActiveSheet.Range("D17").Value
End With

I've chucked it into a Private Sub Worksheet_SelectionChange(ByVal Target As Range) for the sheet and all seems to work rather well.
Phew! That saved a bit of head scratching!