Combining sum result from multiple tables

tomsov

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

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,629
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

New Member
Joined
Mar 31, 2017
Messages
17
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,504
Messages
5,625,183
Members
416,077
Latest member
SJSB

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 MrExcel.com.
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 "mrexcel.com".
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
Top