+ Reply to Thread
Results 1 to 3 of 3

Relationship setup to support pivot tables connecting mutliple sheets

  1. #1
    Registered User
    Join Date
    01-27-2015
    Location
    Toronto
    MS-Off Ver
    2013
    Posts
    2

    Relationship setup to support pivot tables connecting mutliple sheets

    Hi - hope you can help me here.
    I have 4 worksheets (N,S,E,W), each with an identical table that contains 2 columns: Column 1 "Description", Column 2 "Qty"
    On the 5th worksheet I want to create a pivot table that I can have one list of descriptions on the x-axis, and then combine the qty's from tables from the other sheets.

    The goal is to allow the user to make modifications to the N,S,E,W tables, and use the pivot table to manipulate the data.
    I have created the proper tables (with names) - the pivot table was created to use data from all of the tables.

    What I can't quite figure out is on the pivot chart, when I select the qty field to report for the second table, and then try to add it to the pivot chart, it does not combine the totals and it prompts me that relationships may need to be created.

    I am not sure how to configure the relationships. I know one other option could be to combine the 4 tables into a single table, add a new column that is the N,S,E,W and then group by that column so it is similar but I was hoping I could do it through the pivot charts.

    excel_test.jpg

    (not sure if the attachment worked)

    Any thoughts? (Or did I explain this clear enough?)

  2. #2
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Relationship setup to support pivot tables connecting mutliple sheets

    You really need to aggregate the data into one table - it will make everything a lot easier. I'm not sure you can achieve what you want with just Power Pivot. If you have Power Query, you could use that to create a unified data source for the pivot table.
    Remember what the dormouse said
    Feed your head

  3. #3
    Registered User
    Join Date
    01-27-2015
    Location
    Toronto
    MS-Off Ver
    2013
    Posts
    2

    Re: Relationship setup to support pivot tables connecting mutliple sheets

    Thanks - I was hopeful but I guess there is not a simple solution for this.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] What am doing wrong? Creating a relationship between two tables.
    By BrownBoy in forum Access Tables & Databases
    Replies: 2
    Last Post: 07-18-2014, 11:52 AM
  2. Pivot Tables - need to do separate pivot tables for multiple sheets in same format
    By tconnell1965 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-18-2013, 07:04 AM
  3. Relationship between Cell and Data Tables
    By NeshaNYC in forum Excel General
    Replies: 0
    Last Post: 08-15-2011, 10:55 AM
  4. [SOLVED] Excel Pivot tables connecting to sql
    By Justin in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-04-2006, 12:55 PM
  5. [SOLVED] How to change default page setup for pivot tables?
    By bobs in forum Excel General
    Replies: 1
    Last Post: 01-05-2005, 06:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1