Combining sum result from multiple tables


New Member
Mar 31, 2017
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.

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Please Upload Example file & Desired results with XL2BB ADDIN(Preferable) or upload at free hosting Site e.g. , GoogleDrive or OneDrive & insert Link here.
Upvote 0
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")
.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!
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back