Free Microsoft Excel 2013 Quick Reference

Change pivot table report filter selection from a cell

I am trying to create a worksheet that contains several pivot tables that are displaying information from various tables. They all have at least one field in common. On that sheet I would like to have a pulldown list of possible choices for that field. The user would then select the choice from the list and all of the pivot tables would then filter their results against that choice.
I've found a blog where the author (back in 2008) describes how this can be done by writing VBA code. Since then, MS has released Excel 2007, 2010, and 2011. Can what I'd like to do be done more simply with the newer releases or do I have to go the VBA route?


I created a Pivot Table with multiple selections from a Report Filter. My question is once I make a selection is there any way to name the selection so I can retrieve the same selections from the Report Filter without having to remember and make the same selections each time? Any help would be greatly appreciated. Thank you. Jim

I would like to change a pivot tables report filter, which is a date, to another date using vba.
can anyone provide some examples of changing pivot table report filters using vba.

Hi All,

I have a pivot table where in the "Report filter" values are changed using VBA input. I don't want any of the user to change manually the report filter value from the drop down.

I want the user to have some flexibility on "Row labels" on the pivot, so that they can change the layout by drag and drop for the "Row labels". Due to this reason i can not protect the sheet.

Is there a way to lock "Report filter" using VBA in the pivot table.

Thanks in advance.

Nazim

We build Excel 2010 spreadsheets with pivot tables in them referencing an OLAP cube as the data source. We use manual filters to choose several GL acount numbers. When we are done we save the spreadsheet.

We have reopened several spreadsheets from a couple months ago to over a year ago and when we look at the pivot table the filter selections (check boxes) are pointing to different accounts than they were when we created them. How can we retain the original selections so that we can easily pull up the spreadsheet and refresh to just pull in the latest data for the selected GL accounts?

Thanks!

Hi

i have a pivot table report filter that i cannot sort a-z
i've tried sorting the source data and also right clicking on the first value then choosing display a-z - neither work

has anyone got a solution?

its hurting my head, and when i roll it out it'll hurt the users head :-)

Many thanks

Tease

Hey I am trying to use a macro to change a report filter on a pivot table.
The Report Filter is a set of dates and I want to show only a specified range.

Here is my code:

	VB:
	
 Filter() 
    Application.ScreenUpdating = False 
     
    Dim StartDate As Date, EndDate As Date 
    StartDate = ActiveSheet.Range("D1").Value 
    EndDate = ActiveSheet.Range("D2").Value 
     'takes the Date values from the sheet
    Dim pt As PivotTable 
    Dim pi As PivotItem 
    Dim pf As PivotField 
     
    For Each pt In ActiveSheet.PivotTables 
         'goes through each pivot table on the sheet
        Set pf = pt.PivotFields("Date")      For Each pi In pf.PivotItems 
         'goes through every item in the field "Date"
        If Not pi.Name = "(blank)" Then 
             'ignores blanks and then checks if date is in range
            If pi.Value < StartDate Or pi.Value > EndDate Then 
                pi.Visible = False 
            Else 
                pi.Visible = True 
            End If 
        End If 
    Next pi 
Next pt 
 
Application.ScreenUpdating = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
If you've seen this problem before then you might know the error I'm getting is

Run-Time Error '1004':

Unable to set Visible Property of the PivotItem Class
I have scoured pages and pages of forums and help things I have found this on my travels

http://support.microsoft.com/default...22&Product=xlw

But it seems to me all the workaround does is stop the error without allowing you to do what you origionally intended.

I have tried numerous solutions including having autosort disabled and activating manual updating using this code around the property updates:

	VB:
	
 
pf.AutoSort xlManual, pf.Name 
pt.ManualUpdate = True 
 
pi.Visible = False 
 
pt.ManualUpdate = False 
pf.AutoSort xlAscending, pf.Name 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
But none of these have worked for me.

I am using excel 2007 on Windows XP with visual basic 6.5

Please Help
Glenn

Hi,

I hope someone can help me with this. I've prepared an excel file with a pivot table. Now I would like to change the Pivot "Report filter" by using Textbox on Userform. Is this possible and can anyone help me with this? I've attached an excel file as an example. I've all looked on internet but couldn't find any solution.

Any other suggestion I'll appreciate.

Thanks
MMor

My pivot table applies a report filter based on a date column in the source data.

The problem is the date entries in the source data include time of day and there are several entries per day.

When I go to filter the pivot table I must select each date option individually to see the entries on one given day.

How can I roll up the filter so that I can select all entries on a day similar to the auto-filter feature for dates that allows you to drill down the relevant level of detail?

Thanks

I have a spreadsheet that has some cells with no data in them.
Some will never have data, others will as the project progresses.
The pivot table report puts the word (blank) in the report for those cells.

The only solution I have found is to go to each empty cell in the source
data sheet and hit the space bar to force a character into each cell. This
does work but is extremely tedious ( and time consuming). Some other users
(my bosses) of the file add new rows, but are not willing to spend time
fussing around like this.

I tried these two suggested solutions, neither solve the real problem.

1) If I right click the cell and select hide the whole row disappears. I
want the row displayed.

2) In the formatting options menu 'FOR EMPTY CELLS SHOW - _____ " I
checked the box and put various characters in the box. I put the characters
inside quotes, not inside quotes, tried numbers, special characters, just a
blank, any combination I could think of - but the display never changed
from the word (blank) in the pivot table report.

Is there a practical way to change the way these blank cells display in the
Pivot Table.

I have a spreadsheet that has some cells with no data in them.
Some will never have data, others will as the project progresses.
The pivot table report puts the word (blank) in the report for those cells.

The only solution I have found is to go to each empty cell in the source
data sheet and hit the space bar to force a character into each cell. This
does work but is extremely tedious ( and time consuming). Some other users
(my bosses) of the file add new rows, but are not willing to spend time
fussing around like this.

I tried these two suggested solutions, neither solve the real problem.

1) If I right click the cell and select hide the whole row disappears. I
want the row displayed.

2) In the formatting options menu 'FOR EMPTY CELLS SHOW - _____ " I
checked the box and put various characters in the box. I put the characters
inside quotes, not inside quotes, tried numbers, special characters, just a
blank, any combination I could think of - but the display never changed
from the word (blank) in the pivot table report.

Is there a practical way to change the way these blank cells display in the
Pivot Table.

Good morning,

I have a predefined Pivot Table report where users use a userform to change the report filter. The textbox on the userform populates a hidden cell in the workbook which then triggers a worksheet change event to update the report filter cell. If the user enters a wrong value, I get a "No item of this name exists in the pivot table report" message which allows you to hit OK to rename the report filter. Can I write code to intercept this message with my own message box that allows the user to hit OK and start again?

Thank you in advance

Hi all,

I am trying to find a way to change a page field on a pivot table with the selection made in a combo box. I am trying to change the "P.5256" to whatever the user selects in the combo box.

Any Ideas on how to do this?
 
Sheets("Selected Points Charts").Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.PivotLayout.PivotTable.PivotFields("Point ID").CurrentPage = _
        "P.5256"
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlLinear, Forward:=0, _
        Backward:=0, DisplayEquation:=False, DisplayRSquared:=False).Select
    Windows("Points Statistics 2.xls").SmallScroll Down:=21
    ActiveWindow.Visible = False
    Windows("Points Statistics 2.xls").Activate
    ActiveSheet.ChartObjects("Chart 2").Activate
    ActiveChart.PivotLayout.PivotTable.PivotFields("Point ID").CurrentPage = _
        "P.5256"


I have a pivot table and it has a filter which does filtering by month. I am trying to make sure that the filter is set depending upon the present month that can be looked up from another cell which uses the Today() function. Kindly let me know as to how I can assign the pivot table to filter based on a cell value.

Thanks,
Booo

Hi All,

can someone show me if there is an option in Pivot tables to show all data under each column? I often use hardcoded data from a pivot table and require all the fields to be populated.

In essence, I want it to go from Table 1 to Table 2. Table 1 is taken from a pivot table and Table 2 is a hardcode copy and paste from the pivot table.

Table 1:
Sum of 08-09 ESC. BUDGET RC CODESACTIVITY CODEProcess GroupEE CODEClassificationTotal76204202Overhead Vehicle Costs593SLA vehicles64102.54400People Development523Materials & Services2686.2580Materials & Services6105581Materials & Services12210582Materials & Services3663583Materials & Services2442

Table 2:
Sum of 08-09 ESC. BUDGET RC CODESACTIVITY CODEProcess GroupEE CODEClassificationTotal76204202Overhead Vehicle Costs593SLA vehicles64102.576204202Overhead Vehicle Costs523Materials & Services2686.276204202Overhead Vehicle Costs580Materials & Services610576204202Overhead Vehicle Costs581Materials & Services1221076204202Overhead Vehicle Costs582Materials & Services366376204202Overhead Vehicle Costs583Materials & Services2442

Thanks champs
Nick

Hi All,

I have a problem, where I want the user to be able to make a selection from a dropdown list, and once that selection has been made that Excel changes the image...

Lets say I have my options (only two of them), as "Cat", "Dog", and the user selects "Dog", I want the "Dog" image to display...

I think that this might require VBA, and am not adverse to using VBA, but would prefer that it doesn't go nuts and hide and unhide sheets it will really confuse some end users to no end!!!

Any help is appreciated!!!

Thanks

I am using a Pivot Table to view data from a very large database. The Pivot
Table has the following fields: Customer, Quarter and Sales. There are many
duplicate Customer sales in the database.

I would like to create a Pivot Table that calculates the # of unique
customers in each quarter. If I use the Pivot Table Count function it double
counts all of the duplicate Customer entries. Can I create a Calculated Field
that accomplishes this?

I have tried using this formula that I found in a previous string,
“=SUM(IF(LEN(Sheet1!A2:A100)>0,1/COUNTIF(Sheet1!A2:A100,Sheet1!A2:A100)))”,
but since I am using it in a Pivot Table calculated field I used the
following format: “=SUM(IF(LEN(Customer)>0,1/COUNTIF(Customer, Customer)))”.
Where “Customer” is inserted from the Pivot Table field list. I get a “The
function contains an error” message.

Can you help me understand what I am doing wrong? Thanks!

--
Mike Struckman

Hello

I have data in excel sheet as:

WORK_ID PROJECT START_DT END_DT
1001 P1 1/1/2006 1/10/2006
1002 P1 1/25/2006 2/20/2006
1003 P1 3/15/2006 3/20/2006

I want to have a report that will give me average time taken to finish a work in a project. That is, I have to take average of (end_dt - start_dt) for a project group. How do I do this in a pivot table report. When I use a calculate field to get the days worked for a work_id using DATEDIF(START_DT, END_DT, "D") then excel computes only sum on that (pivot table report always uses sum on calculated field). Other solution is to add a column in my source data to compute the difference of start and end dates and then I can use average. I would like to see this computation by using Pivot table report and the source data only in the above format. Report should look as:

PROJECT AVERAGE TIEM TO FINISH A WORK
P1 13.33

Thanks,
Kishori
(615) 253-4734

I am using a Pivot Table to view data from a very large database. The Pivot
Table has the following fields: Customer, Quarter and Sales. There are many
duplicate Customer sales in the database.

I would like to create a Pivot Table that calculates the # of unique
customers in each quarter. If I use the Pivot Table Count function it double
counts all of the duplicate Customer entries. Can I create a Calculated Field
that accomplishes this?

I have tried using this formula that I found in a previous string,
“=SUM(IF(LEN(Sheet1!A2:A100)>0,1/COUNTIF(Sheet1!A2:A100,Sheet1!A2:A100)))�,
but since I am using it in a Pivot Table calculated field I used the
following format: “=SUM(IF(LEN(Customer)>0,1/COUNTIF(Customer, Customer)))�.
Where “Customer� is inserted from the Pivot Table field list. I get a “The
function contains an error� message.

Can you help me understand what I am doing wrong? Thanks!

--
Mike Struckman

Depending on what I select from a cell containing a data validation
list, a predetermined pdf file is embedded into the spreadsheet and
saved.

This will then be emailed and the recipient have the ability to open
the attached pdf document.

Is this possible and if so how can it done?

Hi,

Do you have an idea how I can filter automatically a pivot table by getting data from a cell and thus using this data to filter the pivottable?

For example, on cell A1 I have EU, and I want to use a region filter for a pivot table. How can the pivot automatically detect that A1 is EU and therefore automatically filter the pivottable to EU values only?

Thanks in advance!

vasco

I have 3 pivot tables in my workbook with the same data source. Each pivot table is on a different worksheet. I want all pivot tables to automatically update the filter when one of the pivot table report filters is changed.

The macro I am currently using to create the pivot tables from my raw data is a combination of a bunch of macros I have stored in a blank excel book that runs on excel startup, so that I can use the macro on any raw data set.

If there as any code or formula I could use to auto update my report filters that would be extremely helpful.

Thanks,
J

Hi,

I am trying to create pivot tables from data gathered in a database, but I am having problems getting Report Filtering to work the way I want it to. The dates from the database are in d/mmm/yy format. When I try to use the dates as my pivot table filter, I get every single day as a selectable option. What I really want is to only have month and year (displayed as "mmm-yy" or similar) selectable in the drop down Report Filtering menu. When I do a text conversion for the dates as "mmm-yy", the pivot table will only organize the dates A-Z, which is completely useless. If I change the filtering in the original data to a mmm-yy format, the pivot table still displays by exact date.

How can I format my pivot table so that the Report Filter drop down menu is arranged by date, displayed only as mmm-yy, not d-mmm-yy?

Hello,

I have a pivot table VBA script from http://projectdistributor.net/Releas...?releaseId=358 which helps me in synching the pivot page fileds from one sheet. My pivot table has multiple pivot tables (20 of them ) connected to OLAP cubes. Evey time I change a report filter in one page it will get synched with all other sheets and this happens automatically and time consuming.

I am looking for a way to control this code via a command button so that the filter synch will happen only when I click the command button.

Could someone please help ???

Regards
KP

Hello,

I have a data source with several columns that may contain data I want. For
example, there are multiple stakeholders for each project and the stakeholder
i am interested in may appear in "stakeholder 1" or "stakeholder 2" or
"Stakeholder 3" or so on.

I create a pivot table and add the stakeholder fields to the Report Filter,
but the table "AND"s the columns, so the pivot shows only rows where my
stakeholder is named in all stakeholder fields selected. Can I change this
so the report filter does an "OR" and shows all rows where my stakeholder
appears?