Get totals from various pivot tables

gmckenzie

New member
Joined
Jan 8, 2021
Messages
3
Reaction score
0
Points
0
Excel Version(s)
365 and 2010
New member here.

I've got a set of pivot tables based off of data queries from a non-sql database. The queries and pivot tables are in Excel 2010 on a terminal server (only place I can do the query) and the pivot tables are showing cash needs on a weekly basis based on expected receipt dates for PO's. I want to combine 6 of these pivot tables into a summary.

Pivot tables look like (1 for each store)
PO Number week1 week3 week6 etc.
12345 100
12450 300
12700 400
12999 250
total 100 700 250


I then want to summarize these by week#, but each store does not have product coming in the same week. So some might have week1 and others won't. My summary will list all weeks.

Thinking something like hlookup to compare the week# and pull the total, but I can't seem to make both work.

Is this something that can be done in 2010? Or should I try to connect to the query spreadsheet from my desktop 365 version and play in Power query? I do have power query added to the 2010 version as well.

Thanks for any help
 
I would combine the tables either in Power Query or Power Pivot and then Pivot the Data.
 
When combining the tables, how can I get an identifier added to each table?

I still need to see the cash needs by store, so the resulting combined table would still need to be separated by store.
 
At this point I think we need some sample data uploaded that we can manipulate. In addition to the source data, please mock up a solution so we can clearly understand your needs and expectations.
 
I appreciate the offer to help, but I did figure it out last night.

I've got 6 ODBC queries (could have used Power query, but I'd already built them). so I set up a connection only query to each one, added a custom column to designate the store and merged (append) them together into one table. Ran my Pivot table on that and am very happy with the results.
 
Back
Top