From: kathy.pinkerton on
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))
From: Roger Govier on
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)))


--
Regards
Roger Govier

kathy.pinkerton(a)waters.nestle.com wrote:
> 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))
From: Roger Govier on
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)))

--
Regards
Roger Govier

kathy.pinkerton(a)waters.nestle.com wrote:
> 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))
From: Gary Brown on
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)

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"kathy.pinkerton(a)waters.nestle.com" wrote:

> 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))
From: Gary Brown on
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
'/=====================================/





--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"kathy.pinkerton(a)waters.nestle.com" wrote:

> 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))