Results 1 to 6 of 6

Thread: Get totals from various pivot tables

  1. #1
    Neophyte gmckenzie's Avatar
    Join Date
    Jan 2021
    Posts
    3
    Articles
    0
    Excel Version
    365 and 2010

    Get totals from various pivot tables



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    291
    Articles
    0
    Excel Version
    2019
    I would combine the tables either in Power Query or Power Pivot and then Pivot the Data.

  3. #3
    Neophyte gmckenzie's Avatar
    Join Date
    Jan 2021
    Posts
    3
    Articles
    0
    Excel Version
    365 and 2010
    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.

  4. #4
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    291
    Articles
    0
    Excel Version
    2019
    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.

  5. #5
    Neophyte gmckenzie's Avatar
    Join Date
    Jan 2021
    Posts
    3
    Articles
    0
    Excel Version
    365 and 2010
    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.

  6. #6
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    291
    Articles
    0
    Excel Version
    2019
    Glad to hear that you solved it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •