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!

Find more posts tagged with

Accepted answers

All comments

Sameer Karkhanis

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".

bgood

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!

S Wood

Agree this is an issue I have encountered

Gabriel Barrera

@bgood

Me too! I'm surprised this issue hasn't been addressed.

SModesitt

This issue of pivot tables not updating immediately still persists. I have the pivot set up for going to and from sheets. I have it set up for Update Immediately. In order for new rows in the source sheet to be reflected in the target pivot table, I have to run it manually. Once, when I timing it to see if updates would happen in an hour, it did not run. When I kicked it off manually, I received an error i had never seen before, so had to delete all rows in the target sheet so that it would create the pivot table from scratch.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.