Apr 27 2019 11:52 AM
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.
Apr 27 2019 05:27 PM
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
Apr 28 2019 07:27 AM
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.
Apr 28 2019 04:49 PM
Apr 29 2019 04:09 PM
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!
Apr 29 2019 06:07 PM
Apr 29 2019 06:38 PM
Says I need at least 2010 SP1, which I do not have :( All updates are controlled by our HQ IT dept.
Apr 29 2019 06:49 PM
Apr 29 2019 07:27 PM
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 :)