Forum Discussion
Can pivot value field be auto-updated?
I have a pivot table where the values fields are dependent on formulas. Is there away to auto-update the value in the Values field when the formula value updates? I need to have the error added to the value field so that it updates when the end-user updated the result, it populates.
11 Replies
Just to clarify, are you saying that when a new item id added to the columns in your source table of data you'd like it to automatically add to the values field of the Pivot Table?
Do you have a version of Excel with Power Query and Power Pivot (Excel 2016 or Excel 2013 with addins for Power Query and Power Pivot) as this could be the simplest solution?
Power Query can "unpivot" your source data so your columns become rows and that then makes it easy to drop your codes into the Rows field rather than dragging manually one at a time into the values field
Here's a quick video if you haven't come across Unpivot Before
When the end user adds a new UPC, the #N/A in the values field goes away and the resulting product code appears in the top of the fields list waiting to be added. I'd like the newly added value to automatically be added to the values field in place of the #N/A.
I'll definitely also check out Power Pivot - looks pretty useful.
- OK, so yes "Unpivotting" the data using Power Query and loading it to Power Pivot will likely be the best solution.
If you have Excel 2016 and can send me a small mocked up data set I could pull together a quick illustration for you
