Is there a way to automatically update a pivot based on new row values in source sheet?
Current situation...this pivot is feeding a dashboard
Pivot rows are pointed towards column values in the source sheet that is restricted to dropdown values
Only a subset of the possible dropdown values have been selected in the source sheet. For example...
Dropdown has 10 values, but to date based on entries only 6 exist in all the source sheet records
If I run pivot based on current state everything works fine, but...
If a new record is added to the source sheet with one of the four yet to be used dropdown values, I have to rerun the pivot to update the pivot sheet, update the dashboard Data Source to pickup the new row and count.
Is this the only way to do this? I started to go down the Countifs path, which worked when the source sheet dropdown list was single select...does not work with multiselect dropdowns...which the pivot can do.
Thanks in advance!
Answers
-
If you are saying that you want the pivot to reflect the changes immediately upon addition of new data then you can set the execution frequency in the Pivot setting to either "Update Immediately" or "1 hour".
-
Thanks, Sameer. Yes, I set the frequency of all my pivots to Update Immediately because I want my Dashboard to be as real time as possible. I struggled with how to explain my current challenge without getting incredibly lengthy in my post. The update works fine if the rows in the pivot stay the same...it does what's expected which is update the counts of the existing rows. My challenge is finding a way to get the rows to dynamically update based a new row value being introduced that was not in the source sheet data when the pivot was originally created. Let me try visuals...
This is being used to manage a product backlog...the pivot is feeding a stacked bar chart that is showing Product Category by Status...
The vertical axis is being derived from the Product Category column in the source sheet. In the source sheet it is a dropdown limited to only those values. But, there are more values to choose from than have been used; therefore they are not in the data when the pivot was generated. In this example, there are 18 values in the graph derived from the rows in the pivot. There are 20 total product categories to choose from.
So the challenge is when one of the remaining unused values is used the source sheet now has 19 of the 20, but the pivot will not pick it up unless 1) regenerate the pivot to pick up the row and 2) update the chart Source Data to pickup the extra row.
In the end it is not the end of the world in that I could setup automation to alert me that unused values have now been used and that I need to go update the pivot and dashboard. Thanks again for trying to help!
-
Agree this is an issue I have encountered
-
Me too! I'm surprised this issue hasn't been addressed.
