Getpivotdata Using Date As A Reference
May 22, 2006Trying to use a the getpivotdata fomula with date as an argument, refer to with a cell. e.g. =GETPIVOTDATA( 'May Table'!A1, "SICK " & S20). where S20 is a date format
View 2 Replies
ADVERTISEMENT
Variable Reference In GetPivotData Function/Formula
May 28, 2008I am trying to do a "getpivotdata" formula on a cell in certain file that gets info from a pivot table on another file. However the name of the second file (the one where the pivot table is found on) might be different each time so I created a variable for the second file name but I can’t figure out a way to use this variable in the get pivot data formula as I am not very experience with VBA.
Template = Application.InputBox("What is the name of the file ending with (.xls)")
If Template = False Then
cont1 = MsgBox("Please write the name correctly using (.xls)!", vbOKOnly)
If cont1 = vbOK Then
Exit Sub
End If
End If
Date Range: Reference A Single Date And Output Date Ranges
Oct 11, 2008I need to create formulas that reference a single date and output date ranges. The objective is to have a person input a Monday date in any given month and receive a four weeks out worth of dates and ranges. For example: In a lone cell, the person inputs 10/13/08. Automatically, the sheet produces the next full week range: October 19 – October 25 in a single cell and also produces a cell for each date. Example: Sunday 19, Monday 20, Tuesday 21, etc…. It should look like:
Monday Date:
10/13/08
October 19 – October 25
Sunday 19
Monday 20
Tuesday 21
Wednesday 22
Thursday 23
Friday 24
Saturday 25
and then repeat for three more weeks. I thought I had it figured out until the month changed. The dates continued in October instead of adding a month. This report will be ran weekly, so simply adding a +1MONTH to some cells will not benefit me as I’ll have to change the formula every week. I want the formula to compute the data without any manipulation over the next several years. The only change will be the Monday date.
Getpivotdata Function
May 3, 2007I'm struggling to get the "getpivotdata" function working correctly.Sample book attached.
I'm trying to return the data that is held within the pivot table to a separate part of the sheet.In column D I have week numbers and in column E the qty(which is currently empty) How do I return the qty in column E from the data in the pivot table (column B) relative to the respective week numbers.
GETPIVOTDATA Return 0 Instead Of ERROR
Apr 8, 2009Is there any more efficient way to get a formula that tries to get pivot data to return data to show zero, rather than ERROR, than this?
=if(iserror("the get pivot data formula"),0,"the get pivot data formula")
I am trying to squeeze some more effenciency into a ridiculous spreadsheet, and I hate that this formula has to do 2 lookups and an if statement. If this really is 2x the work, I was wondering if there is a more efficient way?
Getpivotdata From A Separate Worksheet
Feb 23, 2010i am working on a project where i need to get data from a pivot table that resides on a separate worksheet named "Pivot_by_Group".
I have a worksheet "Budget vs. Actual" and I need to pull data from the pivot table to populate the Actual values.
How can i do that and one more thing, hopw can i suppres errors (#NAME, #REF, etc), i've used the ISERROR function with no luck.
#REF! Error From GetPivotData Function
Oct 2, 2008Excel 2003, WindowsXP
When I type = and then click in the pivot table under group1, I am recieving a #REF error. It strange because when I do the same thing in another group (ie. group 2 or group 3, etc.) in the same pivot table I don't recieve the error?
Hide Or Prevent #REF! In GETPIVOTDATA
Sep 6, 2007My created pivot table chart is a "sales funnel" that has 8 field list in the title section used to slice the date (i.e. per quarter, per sales person, per product, etc.). When a combination of selections results in no data, the pivot table dynamically removes this column or row. The GETPIVOTDATA function returns a #REF! because of the invalid reference. How can I lock the pivot table to always have all columns and rows or can I return a zero instead of the #REF!? When the #REF! occurs in a @sum range, the #REF! is returned instead of the sum of the numbers.
View 3 Replies View RelatedDynamic Named Range With GETPIVOTDATA
Jun 18, 2013I want to create a charts on a pivot table, but I don't like the way Pivot charts function and the limitations they have.
So I want to create a normal charts that is dynamic and based on a pivot table.
In order to do so, I want to create dynamic named ranged for the data in the pivot table to use for the data series for the chart.
I can reference the week numbers (headers in the pivot table) for one data series and get the data by this formula:
=OFFSET(Weekly!$C$15,0,0,1,COUNTA(Weekly!$14:$14)-1)
$C$15 is the first cell that holds the data for the series.
Row 14 holds the week numbers (headers in the pviot)
However, I want to make that formula even more dynamic by having it reflect the changes done to the pivot table who's cells it references to.
So how do I do this using the GETPIVOTDATA function? (or some other function that makes it dynamic and reflect changes done to the pivot table and accounts for the row numbers with the data can change)
If I generate the GETPIVOTDATA formula and try to substistute the fixed cell reference for $c$15 with it, the OFFSET formula for the dynamic range gives an error.
And how can I grab the column headers (week number) dynamically?
Attempt To Extract Data, GETPIVOTDATA?
Nov 14, 2005I've attached a spreadsheet which is a basic version of what I'm trying to achieve. I've got a pivottable based on 3 columns of data: Order No, Delivery Date, Value
I want the sum of a given order in a certain month. E.g. the value of Order No. 1001 for Dec '05. I tried doing a pivottable and then using GETPIVOTDATA (which I've not really used before) and failed. I imagine there's a better way I've not attempted yet. Can anyone shed any light? The spreadsheet is attached
Formula Defaults To GETPIVOTDATA Using Mouse
Oct 28, 2006I have this problem with pivot tables:when I write a formula relating to a cell in the pivot table I don't get the usual basic cell refs (e.g. B4/b5) but I get something like GETPIVOTDATA("Vehicle No",$A$3,"Model Desc",...... etc etc
and I cannot copy and paste this formula!
I would like to know if there is a shortcut to use basic Cell refs because it is very boring always type the cell details in manually when using data from a Pvt table
Some weeks ago I had to move from excel 2000 to excel 2003 and It seems incredible to me that moving to a more recent release of excel become a step behind....
also using vba code?
Set Cell References In GETPIVOTDATA Function
Aug 30, 2007Is it possible to replace the Pivit table name/reference in a GETPIVOT function with a cell/range reference? I can on other components of the function call, but not that. For example ... the "hardwired" function call might be:
=GETPIVOTDATA(" Sum of 1991",'[EE_financial data.xls]Sheet1'!$A$3,"Company","ERG","Item"," Depreciation & Amortisation")
I can achieve the same result by externalising the 1991 and ERG, so i replace this with other dates or names, thus:
ERG
1991=GETPIVOTDATA("Sum of "&C13,'[EE_financial data.xls]Sheet1'!$A$3,"Company",D12,"Item"," Depreciation & Amortisation")
.. and this works fine.
However, if I try and replace the '[EE_financial data.xls]Sheet1'!$A$3 with a reference to a cell containing that string, it returns #REF!
Excel 2010 :: Generate GetPivotData Not Working?
Dec 10, 2012In Excel 2010, I can manually enter the GetPivotData function (and it works), but with Generate GetPivotData selected / enabled (in PivotTable Tools --> Options --> PivotTable --> Options), the GetPivotData function isn't automatically generated.
View 1 Replies View RelatedGETPIVOTDATA To Include Hidden Items/Fields
Jul 8, 2006so, for a column, if i reference a getpivotdata to an item's total, it will sum the total being displayed- which can be less than the "real" total if some items have been hidden in the pivot table. what i want is to have getpivotdata (or another function, i don't care what it's called) to always sum an item's total for the entire pivot table range- irrespective of whether certain of it's details have been hidden. ideally, what i'm trying to do is:
sum the total of an item for the pivot table range
sum the total of the column from the original data
compare if they are not equal, the pivot table has not been refreshed
i need to do this without macros. (it's my "solution" to see if macros are enabled or disabled- because my pivot table automatically refreshes data based on macro)
Sum Up Multiple Items For The Same Field Using Cell References In GETPIVOTDATA?
Jan 2, 2014I want to sum up data for two periods (4&5) from a pivot table using GETPIVOTDATA formula. I've only been successful when I manually type the period values 4 and 5 as is shown below:
=IFERROR((SUM(GETPIVOTDATA("Amount",'Transaction Pivot'!$J$3,"Period",{4,5},"Project",$A7,"Category","T&M"))),0)
However, I cannot get the formula to work when I substitute cell references for the period values 4 and 5. Therefore, the following formula does not work:
=IFERROR((SUM(GETPIVOTDATA("Amount",'Transaction Pivot'!$J$3,"Period",{E5,E6},"Project",$A7,"Category","T&M"))),0) where E5 has 4 in the cell and E6 has 5.
Invalid Qualifer Error -setting Variable Using Getpivotdata
Mar 22, 2007We have a very long macro that at the end sends an email to each training coordinator. Within the body of the email, we want to autopopulate the completion status from a pivot table using getpivotdata.
We first try to set the variable but get an error: [compile error:invalid qualifier].
Sheets(Summary).Select
Dim BDCompletion As String
BDCompletion = Application.PivotTableSelection.GetPivotData(A3, "Business Dev Plan Found")
And this is how we plan to incorporate the variable into the body of the email:
With OutMail
.To = "name@company.com"
.CC = ""
.BCC = ""
.Subject = CurrentSheet.Name & " Training Plan Status as of " & Format(Now, "dd-mmm-yy")
.Body = "BD is " & BDCompletion & " complete for 2007 Training Plans as of the date of this email."
.Attachments.Add Destwb.FullName
'You can add other files also like this
'.Attachments.Add ("C: est.txt")
.Send 'or use .Display
End With
SumIf Reference To Date
Jan 18, 2010How can I reflect the correct trainee count for Day1-Batch1?
Currently columns F,G,H computes for expected, actual and total variances of trainee respectively reference to the training date J3.
Since this is a 2-day per batch even, I could not capture the 1st day trainee total of every batch because I divide the sum by 2 (2 day / training batch). The sum is only corrected on the 2nd day.
Attached herein is the sample file.
Date Reference In Formula
Jan 4, 2010I am trying to create a formula, comparing the date in column A to 3 fixed dates, then calculate the value in column C:
=IF(OR(A142=Sheet1!B2,A142=Sheet1!B3,A142=Sheet1!B4),C141-B142+8,C141-B142)
Where Sheet1!B2, B3 and B4 are the fixed dates (5/15/2010, 8/31/2010 and 10/31/2010). I would like to copy the formula into multiple cells, but keep the references to the fixed dates the same. When I do a copy, paste, the resulting formula becomes:
=IF(OR(A143=Sheet1!B3,A143=Sheet1!B4,A143=Sheet1!B5),C142-B143+8,C142-B143)
I need the Sheet1 references to remain the same. Or, can I hard-code dates in a formula?
Using A Cell With Date As Reference
Apr 6, 2012Basically I have a row of dates
so,
B1 - 01/04/2012
B2 - 02/04/2012
etc...
And I have tabs for each of those dates aswell.
Basically I want to pull some figures out of the tabs but use the dates in the reference.
So i need a formula that will look at the date in cell B1, use the date to find the tab and then say find the value of cell G12.
Lookup And Reference For Category And Date?
Jul 21, 2014I'm having serious difficulties with getting together a formula for a little bit complicated lookup.
I have 2 tables. In the first table I have categories in the first column (e.g. red, blue, green), in the second column I have dates (DD.MM.YYYY). And in the third column I would like to get the values from the second table.
In the second table I also have categories in the first column, dates in the second column und values in the third.
I would now like to get the values from the second table that coincide with the categories. And in addition the dates should also be equal to the date in the first table OR the date from the second table should be as close as possible but BEFORE the date in the first table.
Example
Table1
Red 07.08.2005 ???
Table 2
Red 18.04.2005 three
Blue 11.06.2005 one
Red 06.08.2005 four
In this example the value that should be looked up is "four".
Today Date Causes Circular Reference
Dec 8, 2013Several places on my spread sheets I need to compare an action date to today's date.
I use: [Code] .....
in one cell and the fomulas to compare a date in a different cell to that cell.
I get the message "Circular References" for the cell the the above "today()" formula.
I do not get circular references in the cell that refer to the today cell, just the today cell.
How To Reference Cell With Date In Formula
Jul 22, 2011I have a pretty elaborate spreadsheet for my bills. One cell adds up all the bills due during a specific pay period using named ranges. The formula looks like this:
=SUMIFS(NextPmtAmt,NextPaymentDueDate,">=9/9/2011",NextPaymentDueDate,"<=9/22/11")
It works, but when I have to populate cells for upcoming pay periods, I have to go in an edit the dates manually. I'm lazy, and want to simply reference a cell with the date in it instead of typing the whole date out in the formula. What I'd like to do is this:
A2 contains 9/9/2011
B2 contains 9/22/2011
=SUMIFS(NextPmtAmt,NextPaymentDueDate,">=A2",NextPaymentDueDate,"<=B2")
but it doesn't work.
Weighted Percentage With Reference To The Date
Jun 8, 2008I am making a spreadsheet that requires multiple dates and percentages. Is there any way that I can make the percentage that is related to the most recent date weighted more than a percentage with a date from a month or two ago?
For example:
X//Y//DATE
90%//85%//3-March
85%//70%//21-May
87%//90%//6-June
Is there formula so that I can have the 90% and the 87% be the most important (or weighted), the 70% and the 85% be the second most important, and the 85% and the 90% be the least important when I average them out?
MAX Date - Cell Reference Not Changing For Each Row
Aug 10, 2013I'm using the following formula which is entered by using VBA on the worksheet change event but the cell reference 'A2' is not changing for each row, i.e. A3, A4 etc.....
Code:
=MAX(IF( 'Device Use - 4 month Period'!$A$2:$A$20000=A2, 'Device Use - 4 month Period'!$C$2:$C$20000))
Cell Date In Formula Reference
Jan 16, 2008Using cell contents in formula to Populating cells with values from closed workbooks
I am trying to create a summary table of data (e.g. orders) from previous weeks. In the table I am creating, the left hand column contains the Monday date of the week to be summarised along the row. I want the cells to show the values from other spreadsheets when those spreadsheets are closed, but I want the formula to pick up the date of the week that is in column A. I have attached a sample spreadsheet to try and make it clearer.
Date Countdown With Reference To Specific Cell?
Jun 25, 2014I have this formula counting down days until my next fire drill:
[Code].....
However... Every time the drill is done I need to change the date in the formula for the countdown. That I can have it reference a cell instead?
My initial idea was a formula like this:
[Code] .....
(however this code doesn't actually work ...).
Extract Reference Numbers In Given Month With Date?
May 18, 2014I have attached an example spreadsheet which should be relatively intuitive over me trying to explain it.
I am wanting to extract all CJ numbers with the corresponding dates from Sheet 1, into Sheet 2. It has to factor in, engineer name (as stated on sheet 2), the Month (as stated in sheet 2 "May") with the "Attended site" date in sheet 1....
If you see the worksheet it should give you the whole picture with a fair amount of clarity.
Book1.xlsx‎
Change Cell Reference Depending On Date
Jun 11, 2009The spreadsheet attached is a timesheet I use. The last column on the sheet (IV) keeps a running total of time spent on each project. I need a way that i can find out how much time was spent on projects in specific timeframes (eg 01/01/09 - 07/01/01, or 01/01/09 - 31/01/09). my initial thought is perhaps i can have a "from" cell and a "to" cell to input the dates i require and it will total the relevant cells in the rows below.
NOTE! This workbook uses VBA to auto hide any column which are dates previous to today's date so you may need to unhide them to view times logged.
Sumproduct Formula With Date Reference Not Working
Mar 7, 2012I have this formula below that counts all matching cells that fall between two dates (a Monday to the next Monday) where S2 and T2 are the dates (one week apart).
I have 52 colums ie one for each week.
=SUMPRODUCT(($G$3:$G$1000>=S2)*($G$3:$G$1000
Date - Cell Reference To Get Period Of Days Between
Aug 29, 2013I have the below code to get the difference between todays date and the date located on sheet "requests" "B3". The code returns the value: -28040498
Code:
Dim MyODateRng As String
Dim MyODate As String
MyODateRng = Sheets("Requests").Range("B3").Value
[Code]....
So i gues with the first code there is something wrong with the date formating. But i do not understand why the second code does not return simply return the value: 1.