GETPIVOTDATA To Include Hidden Items/Fields
Jul 8, 2006
so, 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)
View 4 Replies
ADVERTISEMENT
Feb 21, 2007
I am using Excel 200o to create a PivotTable but am having problems wiht the Totals. Is it possible to create a PivotTable that includes hidden items in the Totals (row)? I tried the Subtotal Hidden Page Items option but this seemed to do nothing to the Total or subtotal.
View 3 Replies
View Related
Jan 2, 2014
I 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.
View 3 Replies
View Related
Jun 6, 2014
I have some existing code which I did not write, nor do I have the expertise to update. The macro code takes multi rows of data for one person and "flips" the output to be one record per person with columns of data.
The source data looks like this sorted by teacher, Term and Period (columns H and I are the new ones added; the code currently looks through column G only)A
Faculty Name
B
Periodc
C
[Code]......
I just want to add column H and I data to the period course listings by term. So instead of "Term: TM#", just have the display indicate Termcode: Tally/SectSize.... such as (example): "TM1: 20/30"
My Code is as follows:
Option Explicit
Dim wsSource As Worksheet
Dim wsTarget As Worksheet
[Code].....
View 1 Replies
View Related
Jan 14, 2009
I have no problems figuring out SUMIF or SUMPRODUCT with multiple criteria, but I'm trying to help someone with an issue that the second criteria is actually not all-inclusive.
Column B - Lots of numbers, the SUM range
Column C - Yes and No flags. Y or N
C1 = maximum number of matching items to include.
So, =SUMPRODUCT((C2:C100="Y")*(B2:B100)) sums up ALL the Yes rows, but the guy wants to restrict it to the last X matches, and put that X factor in C1. So if C1=5, only the last 5 Y matches are included.
View 9 Replies
View Related
Apr 3, 2009
I am trying to figure out how to do the coding for a command button in a userform I've created. It has 5 textbox fields. I want the command button when clicked to transfer the data in those 5 fields to 5 specific cells in a hidden sheet. Then I also want that button to launch a word document.
Anyone know how I can go about this or where I can go that explains the specific coding?
I've tried searching with mixed confusing results & read through http://www.contextures.com/xlUserForm01.html#Top & http://www.theofficeexperts.com/down...ExcelDownloads examples.
View 7 Replies
View Related
Nov 20, 2009
I am trying to write a list of pivot table page filter pivot items to the Immediate window - but only hidden items. The code below should do the job where the active sheet is a pivot table.
However when I change the page filter pivot items being hidden, the pivot items returned by the macro don't change. It seems to assume that all pivot items are hidden when in fact it may be only one or two. It will work though for pivot row items (pvt.RowFields) and pivot column items (pvt.ColumnFields) where the user changes them.
Does the pf.HiddenItems collection work for RowFields and ColumnFields but not PageFields? If so, is there a pivot field object that reliably holds hidden pivot items residing in the page filters?
Sub ListHiddenPageFilterPivotItems()
Dim wb As Workbook
Set wb = ThisWorkbook
Dim ws As Worksheet
Set ws = wb.ActiveSheet
Dim pvt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
View 8 Replies
View Related
Apr 2, 2014
I need the values that are copied from the template to copy over in text form from the "Data" Tab. Secondly, the master sheet has multiple lines for each vendor. For the area highlighted in red I'd like for it to copy all cells in column C for the vendor and search the vendor by name. Then, move to the next sheet.
View 10 Replies
View Related
May 3, 2007
I'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.
View 3 Replies
View Related
Apr 8, 2009
Is 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?
View 9 Replies
View Related
Feb 23, 2010
i 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.
View 9 Replies
View Related
Oct 2, 2008
Excel 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?
View 6 Replies
View Related
May 22, 2006
Trying 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
View Related
Sep 6, 2007
My 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 Related
Jun 18, 2013
I 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?
View 1 Replies
View Related
Nov 14, 2005
I'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
View 7 Replies
View Related
Oct 28, 2006
I 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?
View 3 Replies
View Related
Aug 30, 2007
Is 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!
View 3 Replies
View Related
Feb 28, 2014
I am trying to format all cells on all sheets (hidden or otherwise) as "Locked" so when the sheets are protected the user can't see the formulas. This macro individually selects every sheet in the book and applys the formatting. Is there a way to modify this code to accomplish the same thing without having it actually select every sheet? The only reason it is an issue is that after running the macro you end up on the last sheet in the book.
View 7 Replies
View Related
Dec 10, 2012
In 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 Related
May 28, 2008
I 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
View 3 Replies
View Related
Mar 22, 2007
We 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
View 9 Replies
View Related
Jun 30, 2007
im looking for a basic macro for workbook copying, all workbook(all hidden, unhidden, and very hidden) sheets.
i know how to do unhidden sheets of course however the hidden and very hidden are giving me some trouble...i need to also copy over all the macro's, buttons, and drop down box's as well..not just values.
View 4 Replies
View Related
Feb 8, 2014
CountryHourDataTotalData
Austria - A10Sum of SeiA51CountryHourSum of SeiASum of SeiT
Sum of SeiT4.88Austria - A10514.88
1Sum of SeiA561562.83
[Code]....
left side pivot created in vb 6.0 & right side pivot table created manually in excel.
i want to generated pivot table using vb 6.0 same as right side pivot.
Set PRange = ws1.Range("R1:Y" & finalrow)
Set PTCache = wb.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)
Set PT = PTCache.CreatePivotTable(TableDestination:=ws2.Cells(1, 1),
[Code]....
View 2 Replies
View Related
Aug 13, 2014
I have a spreadsheet that lists a set of actions and the days they need to be completed.
The due dates are listed in column c and in column h I have a the user choosing "completed" from a drop down data validation.
I have a dashboard on a separate worksheet, showing if the item is complete and the due date.
Ideally I would like to display the dates on the dashboard in red if they are past due, and the box in column h was not marked complete.
I have been going through my books and trying to solve this, but I run into a different issues.
Even if for now If I can get the main worksheet to display the color conditional formatting that would work
View 2 Replies
View Related
Mar 27, 2014
I need to count the total number of times 4 different values appear in a column. This formula works for one value:
=COUNTIFS(Source!$C:$C,$B5,Source!$J:$J,$L$3,Source!$L:$L,$H$1)
Where H1 contains the word Assigned. I need to also find and add to count for matches in I1,J1 and K1 which contain New, Pending and Work in Progress respectively.
View 6 Replies
View Related
Dec 26, 2013
I am wanting to create a list where I can select multiple items within that list and then print only those selected items. I have created something similar thru data validation, but I can't get it to print.
In addition, I would like to be able to subdivide the list into multiple categories, then select items from these multiple categories and print them.
View 3 Replies
View Related
May 23, 2006
If i select 3 items from a list 20 items to be show on the cube this is fine and works however if i then add another item to the list i now have 21 items and for some reason it also adds itself to my 3 selected items to become 4 items. Is there anyway I can get it to be added to the 20 items and not to the 3 items..
View 4 Replies
View Related
Jan 2, 2007
i have an old spreadsheet that i created about a year or so ago.
i managed to make some sheets "very hidden" as opposed to just "hidden" or "visible".
the problem is i can't find them ! i have tried "format, sheet, unhide" but that is greyed out.
i do have passwords for them, but i can't find them to put these in!
View 9 Replies
View Related
Jul 17, 2009
I know how to use array formulae to create a unique list, i.e.{=INDEX($G$1:$G$760,SMALL(IF(ROW($G$1:$G$760)=MATCH($G$1:$G$760,$G$1:$G$760,0),ROW($G$1:$G$760)),ROW()))}
however this is giving all the unique items from column G and I only want the unique items that have a non-zero value in column H as well. This would be the sumif of all instances that would have to be zero. I've tried to crack it and I've tried to search for solutions but so far no joy.
View 9 Replies
View Related