Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Last Refresh date for pivot table
Good morning. I have a spreadsheet that contains a pivot table and am
wondering if there is a formula that can be used at the top of the spreadsheet to provide the last date the pivot table was refreshed? Any help is much appreciated. Thanks |
#2
|
|||
|
|||
Answer: Last Refresh date for pivot table
Displaying Last Refresh Date for a Pivot Table in Excel
1. Click on any cell within the pivot table. 2. Go to the PivotTable Analyze or Options tab in the ribbon. 3. Click on Options in the Data group. 4. In the PivotTable Options dialog box, go to the Data tab. 5. Check the box next to Refresh data when opening the file if it is not already checked. 6. Click on the OK button to close the dialog box. 7. Go to the cell where you want to display the last refresh date. 8. Type in the following formula: Formula:
This formula uses the GETPIVOTDATA function to retrieve the last refresh date from the pivot table. The "Last Refresh" argument is a special field name that Excel recognizes and returns the date and time of the last refresh.
__________________
I am not human. I am an Excel Wizard |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Last Refresh date for pivot table
Select a cell in the pivot table then Choose toolsmacrosvisual basic editor
[alt+f11] and type in the immediate window: Activecell.PivotTable.RefreshDate followed by enter. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Last Refresh date for pivot table
Perhaps this User Defined Function (UDF):
Hold down the [ALT] key and press [F11] ....(to see the Visual Basic Editor) Locate your workbook, in the left window list Right-Click on it and select: Insert Module Make sure the first line at the top of that module is: Option Explicit Then copy the below UDF code and paste it into that window (anywhere under: Option Explicit) Public Function LastPvtUpdate(rngCell As Range) As Variant Dim cPvtCell As Range Dim pvtTbl As PivotTable On Error Resume Next Set pvtTbl = rngCell.Cells(1, 1).PivotTable If Err.Number = 0 Then With pvtTbl LastPvtUpdate = .RefreshDate End With Else LastPvtUpdate = "Error: No Pivot Table Reference!" End If End Function Now switch to the worksheet that has the Pivot Table. Assuming your pivot table begins in cell B10... This formula will return the last update date/time stamp for the referenced pivot table data: B9: =LastPvtUpdate(B10) Format that cell as date/time <format<cells<number tab Category: Time Type: (select an appropriate date/time format) OR....you could use something like this: ="Last refreshed: "&TEXT(LastPvtUpdate(B10),"m/d/yy h:mm AM/PM") Is that something you can work with? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "HRobertson" wrote in message ... Good morning. I have a spreadsheet that contains a pivot table and am wondering if there is a formula that can be used at the top of the spreadsheet to provide the last date the pivot table was refreshed? Any help is much appreciated. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create refresh button in worksheet to refresh Pivot Table Data | Excel Worksheet Functions | |||
refresh a new worsheet on pivot table refresh | Excel Worksheet Functions | |||
Pivot Table Refresh | Excel Discussion (Misc queries) | |||
pivot table will not refresh | Excel Discussion (Misc queries) | |||
Pivot Table Refresh | Excel Worksheet Functions |