Forum Discussion

Scott_P_W's avatar
Scott_P_W
Copper Contributor
Apr 27, 2019

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 t...
excel
Wyn Hopkins's avatar
Wyn Hopkins
MVP
Apr 28, 2019
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
Scott_P_W's avatar
Scott_P_W
Copper Contributor
Apr 28, 2019
Unfortunately, I dont have 2016.
  • Wyn Hopkins's avatar
    Wyn Hopkins
    MVP
    Apr 29, 2019
    What version do you have?
    • Scott_P_W's avatar
      Scott_P_W
      Copper Contributor
      Apr 29, 2019

      Wyn Hopkins 

       

      2010 :(

       

      I've attached a test file that I am using. Basically, the end user will choose a UPC on the dropdowns in row 4 on the first tab and then copy the values to the data worksheet.

      You'll see that I have some of the unpopulated dropdowns in the values field showing #N/A. When the next dropdown in used, the error disappears and the new product code shows up at the top. I'd like it to go straight to the Values field list. A bonus would be if it would also change the description and get rid of "sum of"

       

      Hope this helps :)  Thank you!

Resources