Home

Can pivot value field be auto-updated?

%3CLINGO-SUB%20id%3D%22lingo-sub-489731%22%20slang%3D%22en-US%22%3ECan%20pivot%20value%20field%20be%20auto-updated%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-489731%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EI%20have%20a%20pivot%20table%20where%20the%20values%20fields%20are%20dependent%20on%20formulas.%20Is%20there%20away%20to%20auto-update%20the%20value%20in%20the%20Values%20field%20when%20the%20formula%20value%20updates%3F%26nbsp%3B%20I%20need%20to%20have%20the%20error%20added%20to%20the%20value%20field%20so%20that%20it%20updates%20when%20the%20end-user%20updated%20the%20result%2C%20it%20populates.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F110772iD4AEEA5A36C4DA7B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture1.JPG%22%20title%3D%22Capture1.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-489731%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-489969%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20pivot%20value%20field%20be%20auto-updated%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-489969%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F328945%22%20target%3D%22_blank%22%3E%40Scott_P_W%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EJust%20to%20clarify%2C%26nbsp%3B%20are%20you%20saying%20that%20when%20a%20new%20item%20id%20added%20to%20the%20columns%20in%20your%20source%20table%20of%20data%20you'd%20like%20it%20to%20automatically%20add%20to%20the%20values%20field%20of%20the%20Pivot%20Table%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDo%20you%20have%20a%20version%20of%20Excel%20with%20Power%20Query%20and%20Power%20Pivot%20(Excel%202016%20or%20Excel%202013%20with%20addins%20for%20Power%20Query%20and%20Power%20Pivot)%26nbsp%3B%20as%20this%20could%20be%20the%20simplest%20solution%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPower%20Query%20can%20%22unpivot%22%20your%20source%20data%20so%20your%20columns%20become%20rows%20and%20that%20then%20makes%20it%20easy%20to%20drop%20your%20codes%20into%20the%20Rows%20field%20rather%20than%20dragging%20manually%20one%20at%20a%20time%20into%20the%20values%20field%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere's%20a%20quick%20video%20if%20you%20haven't%20come%20across%20Unpivot%20Before%3C%2FP%3E%0A%3CP%3E%3CA%20title%3D%22Upivot%20Video%22%20href%3D%22https%3A%2F%2Fyoutu.be%2F2c06jmrAb3I%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fyoutu.be%2F2c06jmrAb3I%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-490658%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20pivot%20value%20field%20be%20auto-updated%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-490658%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20the%20end%20user%20adds%20a%20new%20UPC%2C%20the%20%23N%2FA%20in%20the%20values%20field%20goes%20away%20and%20the%20resulting%20product%20code%20appears%20in%20the%20top%20of%20the%20fields%20list%20waiting%20to%20be%20added.%20I'd%20like%20the%20newly%20added%20value%20to%20automatically%20be%20added%20to%20the%20values%20field%20in%20place%20of%20the%20%23N%2FA.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'll%20definitely%20also%20check%20out%20Power%20Pivot%20-%20looks%20pretty%20useful.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-497063%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20pivot%20value%20field%20be%20auto-updated%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-497063%22%20slang%3D%22en-US%22%3EOK%2C%20so%20yes%20%22Unpivotting%22%20the%20data%20using%20Power%20Query%20and%20loading%20it%20to%20Power%20Pivot%20will%20likely%20be%20the%20best%20solution.%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20you%20have%20Excel%202016%20and%20can%20send%20me%20a%20small%20mocked%20up%20data%20set%20I%20could%20pull%20together%20a%20quick%20illustration%20for%20you%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-497230%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20pivot%20value%20field%20be%20auto-updated%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-497230%22%20slang%3D%22en-US%22%3EUnfortunately%2C%20I%20dont%20have%202016.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-498295%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20pivot%20value%20field%20be%20auto-updated%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-498295%22%20slang%3D%22en-US%22%3EWhat%20version%20do%20you%20have%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-501661%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20pivot%20value%20field%20be%20auto-updated%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-501661%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2010%20%3A(%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20attached%20a%20test%20file%20that%20I%20am%20using.%20Basically%2C%20the%20end%20user%20will%20choose%20a%20UPC%20on%20the%20dropdowns%20in%20row%204%20on%20the%20first%20tab%20and%20then%20copy%20the%20values%20to%20the%20data%20worksheet.%3C%2FP%3E%3CP%3EYou'll%20see%20that%20I%20have%20some%20of%20the%20unpopulated%20dropdowns%20in%20the%20values%20field%20showing%20%23N%2FA.%20When%20the%20next%20dropdown%20in%20used%2C%20the%20error%20disappears%20and%20the%20new%20product%20code%20shows%20up%20at%20the%20top.%20I'd%20like%20it%20to%20go%20straight%20to%20the%20Values%20field%20list.%20A%20bonus%20would%20be%20if%20it%20would%20also%20change%20the%20description%20and%20get%20rid%20of%20%22sum%20of%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps%20%3A)%26nbsp%3B%20Thank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-501909%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20pivot%20value%20field%20be%20auto-updated%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-501909%22%20slang%3D%22en-US%22%3ESee%20if%20you%20can%20install%20Power%20Query%20from%20here%3CBR%20%2F%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fwww.microsoft.com%2Fen-au%2Fdownload%2Fdetails.aspx%3Fid%3D39379%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.microsoft.com%2Fen-au%2Fdownload%2Fdetails.aspx%3Fid%3D39379%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-501949%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20pivot%20value%20field%20be%20auto-updated%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-501949%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESays%20I%20need%20at%20least%202010%20SP1%2C%20which%20I%20do%20not%20have%20%3A(%26nbsp%3B%20All%20updates%20are%20controlled%20by%20our%20HQ%20IT%20dept.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-501959%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20pivot%20value%20field%20be%20auto-updated%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-501959%22%20slang%3D%22en-US%22%3EThat's%20a%20shame.%3CBR%20%2F%3E%3CBR%20%2F%3EUnfortunately%20I%20don't%20have%20any%20other%20suggestions%20that%20are%20practical%20here.%20It%20could%20be%20done%20with%20some%20complex%20VBA%2FMacro%20but%20that%20would%20be%20time%20consuming%20to%20develop.%3CBR%20%2F%3E%3CBR%20%2F%3EI'd%20suggest%20you%20repost%20this%20question%20asking%20if%20there%20is%20a%20VBA%20solution%20to%20your%20problem%20given%20you%20don't%20have%20access%20to%20Power%20Query.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-502005%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20pivot%20value%20field%20be%20auto-updated%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-502005%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20appreciate%20your%20help.%20I'll%20be%20upgrading%20to%20a%20new%20laptop%20in%20the%20next%20few%20weeks%20and%20will%20pick%20this%20up%20where%20we%20left%20off%20if%20that's%20ok%20%3A)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-502164%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20pivot%20value%20field%20be%20auto-updated%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-502164%22%20slang%3D%22en-US%22%3Esure%3C%2FLINGO-BODY%3E
Scott_P_W
Occasional Contributor

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.

Capture1.JPG

11 Replies

@Scott_P_W 

 

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

https://youtu.be/2c06jmrAb3I

 

 

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

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
Unfortunately, I dont have 2016.
What version do you have?

@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!

@Wyn Hopkins 

 

Says I need at least 2010 SP1, which I do not have :(  All updates are controlled by our HQ IT dept.

That's a shame.

Unfortunately I don't have any other suggestions that are practical here. It could be done with some complex VBA/Macro but that would be time consuming to develop.

I'd suggest you repost this question asking if there is a VBA solution to your problem given you don't have access to Power Query.

@Wyn Hopkins 

 

I appreciate your help. I'll be upgrading to a new laptop in the next few weeks and will pick this up where we left off if that's ok :)

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies