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

Pivottable Total Include Hidden Items

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

Sum Up Multiple Items For The Same Field Using Cell References In GETPIVOTDATA?

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

Update Existing Code To Include Two More Fields Of Info

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

SUMPRODUCT With 2 Criteria, But One Is A Maximum Number Of Items To Include

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

Transfer The Data In Those 5 Fields To 5 Specific Cells In A Hidden Sheet

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

List All Hidden Page Field Items Of All Pivot Tables In Workbook

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

Editing Macro To Include Multiple Rows And Include Text Formatting For Leading Zeros

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

Getpivotdata Function

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

GETPIVOTDATA Return 0 Instead Of ERROR

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

Getpivotdata From A Separate Worksheet

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

#REF! Error From GetPivotData Function

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

Getpivotdata Using Date As A Reference

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

Hide Or Prevent #REF! In GETPIVOTDATA

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

Dynamic Named Range With GETPIVOTDATA

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

Attempt To Extract Data, GETPIVOTDATA?

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

Formula Defaults To GETPIVOTDATA Using Mouse

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

Set Cell References In GETPIVOTDATA Function

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

Format All Cells In All Sheets To Protection Hidden On Visible And Hidden Tabs

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

Excel 2010 :: Generate GetPivotData Not Working?

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

Variable Reference In GetPivotData Function/Formula

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

Invalid Qualifer Error -setting Variable Using Getpivotdata

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

Workbook Copying (all Hidden, Unhidden, And Very Hidden)

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

Convert Column Data Fields To Row Data Fields In Pivot Table

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

Conditional Formatting IF Function To Identify Past Due Items In Red And Items Completed

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

Count Items In Column That Match Multiple Data Items?

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

Select Multiple Items In List And Then Print Those Items?

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

Excluding Selections: Added To The 20 Items And Not To The 3 Items

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

Very Hidden Sheets Are Very Hidden

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

Creating A Unique List Of Items In Column A That Have A Corresponding Non-zero Value In Column B, I.e. Excluding All Items Where Sumif ColumnB Would Sum To 0

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







Copyrights 2005-15 www.BigResource.com, All rights reserved