Pivot Table Retained Items & Refresh All

[bradams]

I have a spreadsheet that updates based on a user-entered date range. I'm running into two problems.

1. There are some values I need to exclude from the pivot tables. For example, the number of rows of data will change depending on the date range. I have index match formulas where I set the range from row 1 to row 2500 to cover the anticipated maximum number of rows. However, sometimes there are only 1000 rows of data. I need to exclude the 1500 blank rows from the count or it throws off my totals.

So I have gone in and deselected the blank value. However, the problem now is that it doesn't automatically include new values so I have to manually go in and select those values. For example, I have a field of zip codes. If a new zip code appears for the first time, it is excluded from the pivot table by default. So I must go in and select it.

Bottom line is I only see two ways of doing this. Either I change the Pivot Table Option to completely update the Retained Items list each time and then I go through and manually deselect the values I don't want included. Or I continue doing what I am doing now and manually select any new values to include them. Is there any other way to approach this that will both exclude the values I have told it to exclude but automatically include any new values?

2. This one isn't as much of a problem but Refresh All doesn't always refresh all pivot tables. The pivot tables are spread across different worksheets. Sometimes, but not always, I have to go to each worksheet and refresh all while that sheet is active. This seems to be less of a problem when I use the QuickAccess Toolbar (Data -> Refresh All) then it is when I use Ctrl + Alt + F5. However, neither way is 100% effective. Any thoughts?

 

[SpillerBD]

Ideally you don't use a Pivot Table as a LookUp table. You would likely be better bringing your data into PowerPivot and utilize the relationships there to give your Lookup solution.

On the filter, when you deselected the "blank" using the filter you actually selected the current list of items not including the "blank". You want to use the Does Not Equal Option.

Refresh All... Likely what has happened is that there are multiple Pivot caches. Its the cache of data that feeds the Pivot Table. You actually gain better consistency by using the Data Model, which is a by-product of using Power Pivot.

PowerPivot is available to all O365 subscriptions with Desktop application since March of 2018.

 

[bradams]

Thank you. I'm not familiar with Power Pivot so I'll check it out. I'll check out your other tips as well. Thanks for replying.