Round() Function & Pivot Tables

[Duvnjak]

I'm using Excel 2007...does anyone know why the the sum of values for one column in my pivot table would differ from the total sum of my linked table?

I am using the Round(Rand()*100,0) function to determine the values for each cell in this one column. I notice that these values change each time I refresh the page (not sure why it does this). It seems as if the data in the pivot table is always one refresh behind.

i.e.

Table 3+4+5 = 12 (values 3, 4, and 5 are generated by the above function)
Pivot Table 2+3+4 = 9 (values 2, 3, 4 were the values found in the table before the Refresh was applied)

The pivot table should show the same values as found in the table

Thanks in advance to anyone who can help! I hope I was clear enough.

 

[Andrew Poulsom]

The RAND function is volatile, so it updates each time the worksheet is calculated. Calculation doesn't automatically refresh a pivot table, that needs to be done manually. You could try this in the module for the worksheet:

Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
    Me.PivotTables(1).PivotCache.Refresh
    Application.EnableEvents = True
End Sub