• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

CONTEST: Can you make a dynamic summary data table based on multiple pivots?

M

MHudson

New Member
Basic Problem: I want to create a dynamic table that adds rows as necessary, based on a pivot table being refreshed.


In Sheet 1, I have 2 data tables. One is based on nine products with daily prices and the other is different products (15) that are tracked on weekly prices.


In Sheet 2, I have pairs of pivot tables, one for each data table, that reflect different date groupings such as monthly, quarterly, etc..


I need a Sheet 3 that has individual summary tables with each table consisting of all 24 products based on the specific date grouping (monthly, quarterly, etc.) I have it all set up, with GETPIVOTDATA formulas and works perfectly...


Long story short, when pivot tables are refreshed, my summary tables do not dynamically add the new date row. I can drag/autofill down manually, but is there a way to do this dynamically???
 
Ahk. Well, you're close already.


Here's a fast solutions:


1. Move everything on summaries over by one column. IE, dates will now be in B.

2. Make B:B the same as the old formula. So =week. Extend it down to infinity and beyond. Ignore the fact that it's throwing value errors.

3. Column B should be =if(iserror(a1),"",a1)

4. Put your get pivot data's inside a similar formula keyed from a1. So

if(a1="","",GETPIVOTDATA("Average of Al LME Price",Pivots!$A$2,"Date",Table37[[#This Row],[Date]])

5. Hide column A.


This will let you refresh your pivots and have the added dates visible.


On the other hand, this would be a golden opportunity to maybe crack a little vba:

I don't see any method by which the pivot tables are refreshed. Not that you have to do it, but you could add a button which would refresh the pivot tables by name


sheets("somesheet")PivotTables("mypivottable").PivotCache.Refresh and then just add some simple bit of code to the result table and copy the formula down.
 
YES! Works perfect dan_l, thanks for your time and expertise! Just for clarification in your step 2 above, you meant to say A:A instead of B:B, correct?


Also, a related question I should prob start in a new thread...but this works perfect when I convert my table back to a range. I guess my next question is could I accomplish the above but still keep my summaries as a structured table? Technically I can keep the table but having the "=if(iserror" formula creates blank rows with #REF in all the GEPIVOTDATA cells.

I guess Tables are not truly dynamic in this sense, that a new row would be added to the Summary table...


Thanks again dan_l!!!
 
Yes. I meant to say A:A.


You're right. You may have to leave out the table if it's throwing errors on you. I forgot that would extend the cache regardless. Well then, maybe a few bits of vba would get the job done better?
 
I agree, I'm thinking I'll try to keep the tables intact and come up with some VBA to filter out the blanks from the "IF(ISERROR" results in the Dates column(now column C) when the pivots are refreshed. That should work out perfect...keyword SHOULD.


Thanks again for your precise and prompt responses, much help!
 
You must log in or register to reply here.
Back
Top