GETPIVOTDATE Funtion

[kathy.pinkerton]

I have a formula in spreadsheet to pull data from a pivot table. Part of the
formula is a Date function. I would like to copy this formula down the
spreadsheet with the date advancing to the next day, but it only directly
copies the fomula. How can I get this to work without going into each cell
and changeing the date?

=GETPIVOTDATA("Result",Pivot!$A$4,"Result","In","Location","Finished
Product","Line",11,"Sample type2","0 day","MFG",DATE(2010,1,1))

 

[Roger Govier]

Hi Kathy

Maybe
=GETPIVOTDATA("Result",Pivot!$A$4,"Result","In","Location","Finished
Product","Line",11,"Sample type2","0 day","MFG",DATE(2010,1,ROWS($1:1)))

 

[Roger Govier]

Hi Kathy

Maybe
=GETPIVOTDATA("Result",Pivot!$A$4,"Result","In","Location","Finished
Product","Line",11,"Sample type2","0 day","MFG",DATE(2010,1,ROWS($1:1)))

 

[Gary Brown]

Use a cheater row.
Assuming your formula is in Column G, put your dates in column H.
Now change the formula to something like this...
=GETPIVOTDATA("Result",Pivot!$A$4,"Result","In","Location","Finished
Product","Line",11,"Sample type2","0 day","MFG",H4)

 

[Gary Brown]

On a separate issue, I notice you are using the GetPivotData formula that
first appeared in Excel 2002. I don't know about you, but I really dislike
this function. Here's a macro that puts a button on the PivotTable Toolbar
to toggle this function on/off as desired. You only need to run it once,
ever to actually put the button on the toolbar. Why Microsoft doesn't have
the button there by default is anyone's guess.


'/=====================================/
' Sub Purpose: Add/remove toggle for the 'GetPivotData'
' toggle to the Pivot Table Command Bar
' This button allows you to toggle between having formulas
' reference the absolute cell info or be used as the normal
' cell reference.
' IMHO, the GetPivotData should ALWAYS be turned off!
' This feature first appeared in Excel 2002.
'3/21/2007
'/=====================================/
Public Sub PivotTable_GetPivotData()
Dim blnActive As Boolean
Dim ctrl As Object

On Error GoTo err_Sub

'run if Excel 2002+ found
If Application.VERSION >= 10 Then
'set default value
blnActive = False

'look for the GetPivotData button on the command bar
' If found, change variable to true so it won't be added
' again in the next step
For Each ctrl In Application.CommandBars("PivotTable").Controls
If ctrl.Caption = _
"&Generate GetPivotData" Then
'Found the GetPivotData button
blnActive = True
Exit For
End If
Next ctrl

'if button wasn't found, add it to end of the Commandbar
If blnActive = False Then
Application.CommandBars("PivotTable").Controls.Add _
Type:=msoControlButton, ID:=6136 ', Before:=12
End If

'if button was found, remove it from the Commandbar
If blnActive = True Then
Application.CommandBars("PivotTable"). _
Controls("&Generate GetPivotData").Delete
End If
End If

exit_Sub:
On Error Resume Next
Exit Sub

err_Sub:
Debug.Print "Error: " & Err.Number & " - (" & _
Err.Description & _
") - Sub: PivotTable_GetPivotData - " & _
"Module: Mod_PivotTable_GetPivotData - " & Now()
GoTo exit_Sub

End Sub
'/=====================================/

 

[Roger Govier]

Hi Kathy

Maybe
=GETPIVOTDATA("Result",Pivot!$A$4,"Result","In","Location","Finished
Product","Line",11,"Sample type2","0 day","MFG",DATE(2010,1,ROWS($1:1)))