Pivot Table refreshing before underlying data can be recalculated

gooniegirl180

Board Regular
Joined
Aug 13, 2003
Messages
144
I have data which shows stock on hand vs sales vs purchase orders by product code, and I've used a pivot table to make the data easy to interpret. I need to be able to show either one branch or all branches, but one of the columns in the pivot needs to show the total on order for that product code for the entire group.

At the moment, I've used a slicer to drive the pivot table filter which in turn adjusts a calculated column in the underlying pivot data - if all branches are selected, then the orders sit against the first instance of a code, but if a branch is selected, then the formula adds up the ordered quantities for each code and displays that total against the selected branch only. This is the (very inefficient) formula I have in the data range:

=IF(AND(Select_Branch="(All)",AP4<>AP3),SUMPRODUCT(--(Code=AP4),($BI$4:$BI$17000)),IF(Select_Branch=AO4,SUMPRODUCT(--(Code=AP4),($BI$4:$BI$17000)),0))
where column AP contains the product codes, AO contains the branch, and BI contains the quantity the branch has on order for that product. The "AND" statement makes sure the total on order only goes against the first instance of each code, as long as All branches have been selected on the Slicer. There is only one instance of each branch/code combination.

However, once the user selects a branch on the slicer, it seems like the pivot refresh happens before the underlying data can recalculate and I have to refresh the pivot table to get the "Group on Order" column showing data. I don't want the user to have to do this, I want the underlying data to recalculate first, then the pivot table refresh happens automatically.

What code can I use to stop the refresh happening until after the data recalculates? I've been on this for three days now and everything I've tried has failed.

Sorry, I'm unable to post the file or details of the data.

Thanks,
GoonieGirl180
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Forum statistics

Threads
1,181,429
Messages
5,929,842
Members
436,698
Latest member
darshanw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top