Best way to get total figure to update automatically

[kjw298]

Hi, I’m hoping somebody can help me. I have created a tracker at work to show new requests that have come in by category and by client. This gets updated every day.

I have used two pivot tables to show the number of requests that are in progress and the number of requests that are complete.

I have a box at the top to show the total number of requests but having a quick search on Google I consolidated the data to get me the total figure. I have now realised that this doesn’t update automatically as the two pivot tables get bigger each day so the total cells move.

Does anyone know of a way where I could get the number to update automatically? I already have to use the filter for the pivot tables to include new dates as I have unticked the blanks so that they’re not shown.

thanks

 

[severynm]

This is pretty straight forward to do with some simple VBA. First figure out your criteria for refreshing: every time the worksheet changes, when the user goes to a different worksheet, when the user leaves a worksheet, etc etc. (See worksheet events here). Then, within that event, simply call Sheet1.PivotTables("PivotTable1").Refreshtable, or if your data is pulled from a query and you want to refresh that too, ThisWorkbook.RefreshAll.