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
Scott_P_W's avatar
Scott_P_W
Copper Contributor
Apr 28, 2019

Wyn Hopkins 

 

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.

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 29, 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