Using VBA To Return CurrentPage List In Pivot Table
May 17, 2006
I am trying to write code so I can control the PivotFields on a PivotTable from another sheet in the book. I cannot figure out how to get Excel to return a list of the fields in each page field. If I record code to change the Animal PivotField to show dogs, i get the following:
I have a set of accounts (general ledger accounts) that the accounting group posts expenses to. every once in awhile a new account is added. This is captured through a pivot that i have built (sheet A).
on another sheet (sheetB) i want to display the accounts that are shown in the pivot so that i can forecast their future activity.
What i am trying to figure out is how can i make the list on Sheet B change when new accounts are added to the pivot on Sheet A (without simply referencing the pivot table making one cell equal the other)? I am not looking for a data validation pull down, i want a full list of the accounts.
Hi, I desperately need help with a pivot-table and can't find anyone else who's ever had this problem...I have a very large healthcare data table which for simplicity I will describe as follows. In essence the first two columns respectively are (A) location and (B) month. Let's say the third column (C) is the number of cases of the disease kidpox treated in each health centre during that month. The fourth column (D) is the total number of cases of everything treated during that month in each clinic. The fifth column (E) is the proportion of cases of this disease over all consultations for each health centre and month, and the formula in E2 is .....
I have a pivot table which I am interested in filtering in several different ways. The table contains a category for AREA on the Y axis and BLOCKS on the X axis. When unfiltered the tables displays AREA 1,2, and 3 and has a full list of parts. I can then filter the table by AREA to look at one area specifically, and when I do the BLOCKS list shrinks to only those which correspond to that specific AREA.
My problem is that I want to be able to reference on another sheet the value that corresponds to AREA 1 Total regardless of whether the table is filtered to only AREA 1 or if it is unfiltered. I cannot just reference the cell because AREA 1 Total is in a different cell when unfiltered then when filtered. Is there any way to do this?
Note my current selection ("Marysville-Service Center"). Using VBA, how would I go about retrieving the upper level row label - "BLACK" in this case? How would I get the level above that ("16GB"), and etc ("APPLE IPAD MINI7" then "2.Recommended"). How can I tell how many rows are above the current selection...say if I want to retrieve the highest tier?
I'm trying to create a Pivot table with various columns comparing information of executives from various Private Equity Firms. Some of the additional columns are Industry Specialty, Harvard Grad Year, My companies contact, etc . . .
The goal is to allow me to then query this information so if I wanted to see all Bain Capital employees who graduated with an MBA in 1990 and their specialty.
My question comes in trying to set up my source list for the data under Industry Specialty and my companies Contact. Is there a way I can set a value for these cells and mark them as an "AND" in the source if there are multiple contacts or industries that one of the Private equity executives is linked too, that would then allow them to be displayed as so when I create the pivot table?
I'm trying to create what is essentially a report card for our staff. I have all the data in a pivot table and then I am using 'GETPIVOTDATA's in the report template to extract the information, linking the sheets via a cell that contains the staff members name. Easy enough. However for reasons of efficiency and practicality I would like to be able to select the names from a drop down list on the sheet rather than having to type it in manually or maintaining a separate sheet for everyone. The operators will change from month to month and there are approximately 90 staff at any given point in time that will need to be reported on so it really needs to be sourced from the pivot data.
I have a pivot table with a group # on the left, and team members on the right. There are multiple team members in each group, and each are on a separate line in the pivot table, like this:
Group #Team1chrisdawnsally2cassiechrisdawnkathysally3jimjoejohntomGrand Total
I need to find where "sally" is on the team (easy to do"), BUT I need to display the rest of the team members in the group with her, like this:
Group #Team1Sally, Chris, Dawn2Cassie, Kathy, Sally, Dawn, ChrisGrand Total
Using the concatanate function to put them all into one cell and then filtering for "contains" was all I could come up with, but there are 14,000 groups
Originally I considered posting in the Excel / Email area but I know how to automate the email part of this already once I get the data collection I'm looking for. Let me explain. I have a pivot table that consists of reps, accounts, items, and sales data in that order.
For each rep I want to create an email of affected accounts and items with thier respective sales data. Because each rep has a variable number of lines I'm having trouble defining the exact range. I've used a few pieces of code taken from various posts from this forum to start me off but nothing seems to be working, else I'd post what I already have.
The field list does not appear when I create a pivot table in Excel 2007. It works properly if I start Excel in safe mode. I have toggled the field list button in the PivotTable Tools show/hide ribbon and I tried repairing Office 2007 from the control panel.
Im trying to create a 'drill-down' interface with the GETPIVOTDATA command.
I believe (but im not sure) that this will require several different formulas.
e.g., assuming this formula resides in A1, this returns all data in the pivot $A$6 for Monday of 6/1/2009:
=GETPIVOTDATA("Sum of Mon",$A$6,"Week", DATE(2009,6,1))
however for cell A1, if the user wants to drill down, then the required formula expands to the following..in this case we are drilling down to Name=Baby Becket/Ball, Stage=Infant..and so on..
=GETPIVOTDATA("Sum of Mon",$A$6,"Name","Baby Becket/Ball","Week",DATE(2009,6,1),"Stage","Infant","B/L","B","WL",)
Essentially, without writing a bunch of IF's in the formula for A1...is there a way to put these formulas in a lookup table, and then depending on what the user chooses on how they want to analyze the data (e.g., they may select Name, Stage, etc from a drop down list elsewhere on the sheet), the appropriate formula is populate in A1?
In a nutshell: Can the formula of a cell be changed depending on what the selection value is of another cell or list value?
This could probably been done easily via VBA, but if there is a formula or vlookup based solution that would be easier..
I have a huge data set with survey data (sample attached: sample.xlsx). Column headings: survey questions Row headings: respondent identifiers (names) Row data: answers to questions (numeric values, only five answer options 1,2,3,4 or 5, no blanks)
I need to build that kind of pivot table:
Rows - list of particular questions (i.e. questions no. 3, 7 and 12) Columns - list of all diffrent answer entries (eventually, it will be 1,2,3,4 and 5) - it could also be questions in columns and answers in row (no difference) Values - count answer entries (i.e. how many answers "5" are on quesiton 3)
------ UPDATE: explanation added.
The reason I need pivot tables: i'll have to cross analyze multiple sets of questions. I'll have to do such cross-analysis 100+ times, so, writing a formula for each time does not quite work...
I want to compare a hand entered data sheet to my system data. System data is exported in. Then that data is used to populate a pivot table. I use a lookup list that fills in matching data from my hand typed sheet. From there I want to use true/false to see if the data matches. However, the formula will not copy down. I am sure it has to do with the formula holding a value, but I can’t seem to correct it.
Recap 1.System data populates sheet 2.Pivot table pulls info I need. 3.Lookup list cross references hand entered data sheet and puts value next to pivot table. 4.I need to do a true false to see if data matches but can’t get it to work
Example worksheet: On the worksheet you can see that the true false statements are incorrect. The system data and the hand entered data match two times not once.
Is it possible to set up the second level of a report filter based on the value of the first report filter. I have a pivot table that needs two report filters: first is the location and the second is the department. What I want to do is have the user be able to select the location and then only show valid departments for that location. I know that this is possible if I set up a form and have the second field be a query against the first. I can create a calculated field but I cannot put that in the report filter area.
I want to print one sheet for each of the values in a page field drop-down. I can do this by supplying the values individually (code below) but would rather determine the list of values dynamically. Can this be done?
Private Sub pbPrintAll_Click() Dim cix As Integer Dim Ctrct As String cix = 3 While (Sheets("Database"). Range("B" + Trim(Str(cix))).Value <> "") ' Get value from database sheet Ctrct = Sheets("Database").Range("B" + Trim(Str(cix))).Value ' Set CurrentPage value Sheets("Customer P&L Pivot1"). PivotTables(1).PivotFields("Cust 1A_Name").CurrentPage = Ctrct ' Print formatted sheet Sheets("Customer P&L").PrintOut cix = cix + 1 Wend ' Reset Current Page value Sheets("Customer P&L Pivot1").PivotTables(1).PivotFields("Cust 1A_Name").CurrentPage = "(All)" MsgBox "Prints sent to printer." End Sub
- a list of data - a pivottable based on these data
When moving this worksheet this worksheet to another workbook, the pivot table can't refresh anymore. This throws an error message "Reference is not valid". To work around this problem I need to adapt the datasource. The same occurs if the list and the pivot table are on separate sheet, with the added strange behaviour that, when data an PT are split, it is not possible to move both sheet together.
This would not be a big issue if my problem had to be solved manually. The real problem is that I need to move the sheets from a C# program.
I have a complex list of text (a chart of accounts) with various roll ups for subtotals on one worksheet, and monthly download on another worksheet showing the name of the account and the value. I wish to use match the text to return the value in the front sheet and avoid taking the simple route of linking the cells in case additions are added during the year.
I've tried using IF and VLOOKUP formulas without success - i guess becasue I cannot sort the table in descending order?
What would be the best sort of formula to search using the text, and return a value on the same row?
I'm trying to use a List Box to select multiple items on my Pivot Table but I can't seem to get it to work. Its the Report Filter part of the pivot table.
When the list Box is set to fmMultiSelectSingle it work and selects the indivdual items, but I need it set as fmMultiSelectMulti for if i'm also needed to select multiple items. (which doesn't seem to work!)
I've created a pivot table and I'd like to index through each "value" in the page field and then copy the results to another sheet, one sheet per field returned.
I can't figure out if it's possible to index through the list though. Is it possible?
In the "Pivot Table Field List" I have the option to "Choose fields to add to report". I have a large number of fields that I want to choose and "Add to Values" - so it is tedious to select each item individually.My question: Is there any possible way select multiple fields at the same time? e.g.
a "select all" option, orhighlighting a range of the available fields (either by dragging or holding the shift key)I've looked / tried above with no luck.
Any Excel Add-Ins out there that might address this? I'm fearing not since my searches have come up empty.
I'm trying to place a pivot table in an existing sheet in order to have a list of names next to a P&L. I've got several criteria to filter the names, but when I apply the filters to the pivot table, not all the people show up. The weird thing is that the total at the bottom of the pivot table, which is a simple sum of time in a given month per person, calculates the correct number as if all the employees are there.
When I do the same table in a fresh sheet, in the same file, it shows correctly. It's only when I try to put the table in an existing sheet that it abbreviates the list.
I've read a lot of tutorials on how to use pivot tables, but found them either too basic, or too advanced, and never finding any example of what I am looking to do.
I have a database, column A holds names, and column B their phone numbers. I'd like to generate a list of unique names and their associated telephone numbers. I figure I can use a pivot table to do this.
But I do not quite understand how to format the pivot table to put the name in one column and the phone number in the next. I will use this pivot table as a vlookup range. I assume I can do this.
