Here’s the illustration again: OK, so the Power Pivot refresh service has one primary mission in life, and that is to refresh the Power Pivot model. That’s right, it leaves the pivots in the workbook alone. On the resulting dialog you will see the checkbox “Refresh data when opening the file.” By default that checkbox is NOT set.
So by default, those pivots (and/or cube formulas) still contain stale data! You can try this out to see what I’m talking about: take a Power Pivot workbook, open in Excel client and verify the checkbox is not set. When complete, download the workbook and look at this setting again. I’m 99% certain that there is no API on the server for doing this.
Unfortunately, every time I do it I get the result #NA.
I have tried recreating the results from the pivot table in the worksheet next to it, and am able to use VLOOKUP on them successfully, but as soon as I point the VLOOKUP array to the pivot table, I get the #NA result Can VLOOKUP be used on pivot tables? Thanks Also, I cannot move the Pivot Table from the new Worksheet it is created in, to my existing Worksheet. I have a Worksheet with a small amount of data, and when I click the Pivot Table button it automatically creates the Pivot Table in a new Worksheet.
So it is no surprise that many of create multiple pivot reports in one Excel workbook. If you want to learn more about data visualizations that don’t suck, check out my Annielytics Dashboard Course offerings. If you update the data in your underlying Excel table, you can tell Excel to update the pivot table information. This doesn’t mean, fortunately, that you need to go to the work of re-creating your pivot table. Nelson In many circumstances, the data in your Excel list changes and grows over time.The Pivot Table data is fine, but I want it to display alongside the source data.