Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11
Default 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   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up 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:
=GETPIVOTDATA("","'PivotTable Name'!$A$1","Last Refresh"
  1. Note: Replace "PivotTable Name" with the actual name of your pivot table.
9. Press Enter to display the last refresh date.

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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 272
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,118
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Create refresh button in worksheet to refresh Pivot Table Data Ron Excel Worksheet Functions 1 October 13th 07 01:20 AM
refresh a new worsheet on pivot table refresh [email protected] Excel Worksheet Functions 0 February 9th 07 08:39 PM
Pivot Table Refresh pivot table drop down values Excel Discussion (Misc queries) 3 November 12th 06 12:59 AM
pivot table will not refresh CN Excel Discussion (Misc queries) 4 September 18th 06 03:34 AM
Pivot Table Refresh Carl Excel Worksheet Functions 2 August 8th 06 03:42 PM


All times are GMT +1. The time now is 03:48 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"