+ Reply to Thread
Results 1 to 6 of 6

Pivot table from a Pivot Table

  1. #1
    Registered User
    Join Date
    01-21-2009
    Location
    Sydney
    MS-Off Ver
    Excel 2003
    Posts
    3

    Pivot table from a Pivot Table

    Hi,

    For Ms excel 2003

    I am trying to run a pivot table from data generated from another pivot table. Is there a way to select the source pivot table by name? When you select it by cell reference the results fail when a user changes the parameters of the source table.
    Last edited by VBA Noob; 01-21-2009 at 03:36 AM.

  2. #2
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561
    Hi, what you're proposing doesn't seem practical, at least from my own understanding. As you've alluded to, pivot tables resize according to the flexibility the are designed for, also, pivot tables create gaps when a field has more than one sub-field. This data would be lost (qualify under "(blank)").

    More to the point, in my experience, there is nothing additional which can be achieved with a pivot on a pivot which can not be achieved within the original pivot. Perhaps you could post a trimmed down example and we could help you reorganise the existing pivot table to show what you're trying to do with the second.

    HTH

  3. #3
    Registered User
    Join Date
    01-21-2009
    Location
    Sydney
    MS-Off Ver
    Excel 2003
    Posts
    3
    Thanks Charlie,

    I am using one Pivot table to bring in data from MS Access so the user can run various parameters to see the results.

    I then use a second pivot table / pivot chart to summarize the firsts pivot table data down further. To give an overall summary. And allow the user to easily see results without having to rebuild all the parameters again. [IE only need to change the parameters in the first table]

    At present for the second table i use cell references to the first - as you pointed out causes problems because the first table continually changes size. As such rather than use a cell reference to refer to the first table I am looking for a way to use a 'defined name' to refer to the first table.

    If this does not help I will try and reconstruct a snippet -with dummy data.

    Thanks

  4. #4
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561
    I think I know where you're coming from,

    I think grouping data might be of use here - if you select a certain number of items in a field - right-click - group
    Importantly, this creates a new pivot field which can be added to/removed from the table at will. This means complex reorganisations can be juggled easily with the created fields.

    If this saves 'enough' time you might find it faster than two pivots.

    You can absolutely use a defined name to refer to a non-fixed table, assuming your table is on "A3" this would look something like this:
    =offset($A$3,0,0,counta($A:$A),counta($3:$3))
    NB use of $fixed $references is very important.

    This relies on there being no other data in column A or row 3, and, as I mentioned before, goes fully ****-up if the source table has multiple entries per group - creating blank spaces in the table - not only will the data not extract correctly, the source will define too small...

    HTH

  5. #5
    Registered User
    Join Date
    01-21-2009
    Location
    Sydney
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Pivot table from a Pivot Table

    Thanks again for your comments.

    I tried these, the first solution "group by" did not seem viable because of the number of columns I have. I may need to play with this one a bit more.

    The second solution where by you enter a formula into the reference section works a treat, the only problem is that when you close the dialog box the formula is replaced and does not recalculate - ie it does not become dynamic. This may be a possile limitation of excel.

    Your help is much appreciated

    Easterly_shot

  6. #6
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: Pivot table from a Pivot Table

    In which case, I'm afraid, you're doing it wrong... this is a good resource for a bigger discussion of this concept:
    http://www.ozgrid.com/Excel/DynamicRanges.htm

    HTH

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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