GETPIVOTDATE Funtion
in [Functions]
|
From: kathy.pinkerton on 30 Mar 2010 13:41 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 30 Mar 2010 15:57 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 30 Mar 2010 15:58 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 30 Mar 2010 16:01 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 30 Mar 2010 16:08
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)) |