Pivot Table Filter values

Is there a way to "read" what values you have selected for a Pivot Table 
filter in Excel-07? 
I have Report Filter(s) where I may select multiple values for any given 
filter. Being that this is a Report Filter, the values are not displayed in 
the data. For instance, say I have a Report Filter which contains 12 months 
(Jan - Dec). If I select Jan, Feb & Mar in this filter, the pivot table then 
reflects this data. However, short of clicking the drop down to see which 
items I've selected, I have no way of knowing what months are being 
displayed. I just want to be able to write these values to a cell/textbox 
showing something along the lines of...."Selected months - Jan, Feb & Mar".

Thanks in advance
steve
0
Steve8733 (4)
5/28/2009 6:10:01 PM
excel 39879 articles. 2 followers. Follow

5 Replies
373 Views

Similar Articles

[PageSpeed] 34

Excel 2007 Pivot Table
Report Filter
Show selections
http://www.mediafire.com/file/gydno5hdfmt/05_28_09.xlsm
0
5/29/2009 2:32:51 AM
Thanks Herbert for the quick turn around. That is EXACTLY what I needed. 
Thanks again.

"Herbert Seidenberg" wrote:

> Excel 2007 Pivot Table
> Report Filter
> Show selections
> http://www.mediafire.com/file/gydno5hdfmt/05_28_09.xlsm
> 
0
Steve8733 (4)
5/29/2009 12:52:01 PM
Upon further review, The solution given by Herbert did not accomplish what I 
needed but no fault to him. I was not clear on my scenario.

I have my pivot table linked back to an Oracle datafeed meaning the raw data 
is not readily available to scan thru. Besides it has over 250k records.

However I'm close to a soulution with one small problem to work thru. In the 
code below, the line of code "If .PivotFields(FilterName).CurrentPage = 
"(All)" Then" does not recognize I no longer have "(ALL)" selected and 
therefore the IF statement remains true even after i've selected a specific 
value. 

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    rw = 26
    With PivotTables("PivotTable6")
        For Each pvtField In .PageFields
            rw = rw + 1
            FilterName = pvtField.Name
            Cells(rw, 10).Value = FilterName
            If .PivotFields(FilterName).CurrentPage = "(All)" Then
                    Cells(rw, 11).Value = "ALL"
            Else
                Cells(rw, 11).Value = ""
                n = .PivotFields(FilterName).PivotItems.Count
                For i = 1 To n
                    If .PivotFields(FilterName).PivotItems(i).Visible Then
                        If Cells(rw, 11).Value = "" Then
                            Cells(rw, 11).Value = 
..PivotFields(FilterName).PivotItems(i).Name
                        Else
                            Cells(rw, 11).Value = Cells(rw, 11).Value + ", " 
+ .PivotFields(FilterName).PivotItems(i).Name
                        End If
                    End If
                Next i
            End If
        Next pvtField
    End With
End Sub

"Steve8733" wrote:

> Thanks Herbert for the quick turn around. That is EXACTLY what I needed. 
> Thanks again.
> 
> "Herbert Seidenberg" wrote:
> 
> > Excel 2007 Pivot Table
> > Report Filter
> > Show selections
> > http://www.mediafire.com/file/gydno5hdfmt/05_28_09.xlsm
> > 
0
Steve8733 (4)
5/29/2009 5:00:02 PM
Excel 2007
Use .AllItemsVisible = True instead of .CurrentPage = "(All)"
Use & instead of + to concatenate text.
Use (space)(underline) to wrap text.
Except for the event procedure and text concatenation,
the functionality of our approaches is the same.
No speed comparison was done.
http://www.mediafire.com/file/2tnzojyoe20/05_28_09a.xlsm
0
5/29/2009 8:50:23 PM
Thanks Hertbert. Works great!

"Herbert Seidenberg" wrote:

> Excel 2007
> Use .AllItemsVisible = True instead of .CurrentPage = "(All)"
> Use & instead of + to concatenate text.
> Use (space)(underline) to wrap text.
> Except for the event procedure and text concatenation,
> the functionality of our approaches is the same.
> No speed comparison was done.
> http://www.mediafire.com/file/2tnzojyoe20/05_28_09a.xlsm
> 
0
Steve8733 (4)
6/1/2009 2:21:01 PM
Reply:

Similar Artilces:

Access 97
I have a form with a field called Proceed (Y/N) I need this field to complete automatically based upon the values in other fields on the form. e.g. if Field1="Mortgage" and Field2 <>"" then Proceed = "Y" How would I go about coding this? In the form's BeforeUpdate event. If you are trying to check for null, using <>"" will not work for two reasons 1- "" is a zero-length-string and 2 - any type of comparison with null will return null so you need to use the IsNull function. If field1 = "Mortgage" AND Not IsNul...

Query from three tables, problem when adding a record
Hi ! I'm using Access 2002/2003. I have three tables A, B and C. Table A has a primary key, which is linked to corresponding foreign keys in tables B and C. The values of the keys are unique in their respective tables. In all three tables, each value occurs at most once. I am using a query Q showing all rows of A, and the rows of B and C in a linked fashion. The query Q allows me to add a new record two A. And I can also add a new record to B and a new record to C at the same time (in the same query row !). But, in case that I add a new record to A only and then leave the query, open...

Excel Web Service - Multiple Pivot Charts
Hi, I have an Excel2007 dashboard, which contains more than 5 pivot charts. Its a testing dashboard, which has got several defect metrics based on same data source. I am publishing these pivot charts to Sharepoint using excel web service. Currently I need to fiter all pivots one by one to see the charts for different parameter. something like (if I need to select the defects for different releases, i select them one by one in all pivots). I know, i can create a simple macro to trigger updates to all pivots. But the problem I am facing is - After I publish the pivot charts to Sharepoint. Mac...

Calculated Items in Pivot Tables #2
Dear all, I am new to excel pivot table and I would like to ask you all a question regarding the calculated items/fields in pivot table. I am making a pivot table whose data source is shown below: Team Person Amount_Sold Price_each Paper A 50 35 Paper D 60 25 Pen D 500 4 Desk C 20 400 Desk A 10 700 Pen B 700 3.5 Clip B 500 2.5 Clip C 600 2 Desk B 15 600 I am thinking if it is a way to ad...

tables
i have created a table in excel but want the edges of table to be slightly curved instead of being a right angle....is there a way of doing it? -- Sam Not sure what you meant by "table." How did you draw/create the table? Have you checked out AutoShapes from the drawing toolbar? = Autoshapes>Basic shapes>first one second row is a rounded rectangle. = Draw the rectangle then right-click ...... Format AutoShape>Colors and = Lines tab>Fill - color - no fill or transparency 99%. Grid lines = optional. Is this what you want? Epinn=20 "Sam" <Sam@discuss...

Default Value from another table
I want to assign a value in a form to a value from another table. I want to be able to do this for many forms (using the same value from that table). Why? Well, here is the setup: I have a login screen in the beginning (has a combo for the usernames, and text for the passwords). I have another table (table1) the records what username you selected. I want to use that value, and use it as the default value for the "Name" fields in forms the individual will use (that way they don't have to type in their name, and/or have a combo to select their name (where they can lie and chose s...

goal seek and negative values
im using goal seek and from time to time it returns a negative value. how do i prevent this? i have set validation rules for the cells so that i cannot manually enter a negative value but goal seek seems to igore this. Steve, Use Solver instead. Put in a constraint: A2>=0. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "steve" <steve@discussions.microsoft.com> wrote in message news:E5787E67-D3C9-4679-A514-8C26B9A75E48@microsoft.com... > im using goal seek and from time to time it returns a negative value. ...

relationship with multiple tables
Hello and thanks for the help. I am using OFfice Xp. Ihave a db with 3 tables. All are correctlly related. When I put all three tables in the query I am not able to change any data. Can NOT add, delete or modify any records. The add new records button is grayed out. What's going on? How do I get to use the data in these tables. Here are the specifics. Student Table -- Primary Key SSN related to the Contacts table with a one-to-one relationship with SSN Contacts Table -- Primary Key SSN -- it is the secondary table to the Student table with the SSN primary key. It is also related to the...

Outlook filtering fails on html formated email
When an email arrives containing words for medical stuff I neither need nor want, my email filter fails to intercept admail that is formated HTML. Viewing the source code reveals that the ad writer has imbeded superfluous code between letters to thwart the filter. For example: 'Visit the online pharmacy for ...' becomes Visi&#116; &#116;h&#101; O&#110;li&#110;&#101; Ph<gclxctkdhszmop>arma<gdubhlmpmkxmwie>cy for yo&#117 Embeded in the html is an actual address 'xxxx@genericforless.com/blah' but somehow Outlook fails to catch thi...

Time Question filtering out weekends and mondays
Im using the following expression in a query to find the average time elapsed between the time a case started to the time it ended. the problem is cases are not worked on saturday, sunday or monday. For our business we want to use an elapsed time that would not count those three days as time. For example if a case is started friday at 8am and finished the immediate wednesday at 9am that should be 49 hours instead of 121 hours. Actual Start Actual End Elapsed: Avg((DateDiff("n",[Actual Start Date],[Actual End Date])/60)) Thanks for your help. On Dec 13, 3:19 pm, Qaspec <Q...

list of figures looses some of its figures once tables are inserte
word 2007 First, this is not an issue with assigning a caption from a picture and the caption isn't in a text box. In fact, I've scoured the internet and found no known issues like what I'm about to describe. My document has 16 figures and 12 tables. I created a list of figures and a list of tables and found that both go missing randomly when any changes are made trying to get them to reappear. E.g., if I'm missing only fig 13, and I test a change, several figures then go missing. Or if I have all figures and begin to insert tables, the list of figures looses ...

Filter data from two worksheets from same excel file
i have a set of account numbers in 2 worksheets( created at diff dates). i want to find out the ones which are not there in the earlier. Both worksheets are saved in a single file. The account numbers are of 11 digits and the each sheet contains hundreds of such account numbers To compare entries in Column A of sheet2 with entries in Column A Sheet1 in your sheet2 (new column G) enter in first data row(say G2)) =COUNTIF(Sheet1!A:A,Sheet2!G2)=0 and Copy down as far as is needed FALSE = These values are on Both Sheets TRUE = These values Are NOT on Sheet1 "anu" wrote: > i...

convert all tables to local tables
Is there a fast way to convert all tables to local tables without doing each one individually. You mean convert linked tables to local tables? Just import everything into the backend and you will have all local tables.... -- Maurice Ausum "Luther" wrote: > Is there a fast way to convert all tables to local tables without doing each > one individually. No. You have to import each table individually. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Luther" <Luther@discussions.microsoft.com> wrote in message news:C4C3...

Linking drop down menus in several pivot tables
I have several pivot tables in a template worksheet and they hav dropdown menus to select the files which contain the data. At th moment I have to go through and change every dropdown menu when I wan to look at new data. I would like for my other pivot tables to link t the option I choose in the first pivot table. Is there a way to d this -- Message posted from http://www.ExcelForum.com You can use the following code, adapted from a posting by Robert Rosenberg. It changes all Pivot Tables if the page is changed on the first PT. You could revise it to suit your layout. As noted in the code...

Add two Columns to a table from another table
It has been a couple of months since I have worked with access and I can't remeber the right way to put the critera. I want to add two columns from table A to table B where the machine name is equal to each other. I know I have to use an append query I just can't figuer out how to set the critera to place the correct data in the right place. Both tables have been imported and both have a column called machine name. I want to add two columns from table A to table B where the machine names are the same. Please help me out. Thanks very much, Greg It isn't clear whether you ar...

How do I get total value data labels in a stacked bar chart?
I have a 3-D stacked bar chart with four series and I want to have the total value in each category be displayed in a data label. Can I do this, and if so, how?? Hi, This should help http://www.andypope.info/charts/StackColTotal.htm Cheers Andy blemerson wrote: > I have a 3-D stacked bar chart with four series and I want to have the total > value in each category be displayed in a data label. Can I do this, and if > so, how?? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info Thanks, Andy. The CFO is thrilled. Heather "Andy Pope" wrote: > Hi, >...

Pivot Table Subtotals/Variance Analysis
I am trying to get a pivot table to show a variance column for the subtotals of 2 sets of grouped data. This is for summarising all financial transactions by period (column) by funding source and cost centre (rows), and then comparing this to the budget amount. I have added a pivot table field using options to find the difference between these 2 groups (transactions and budget). It does return the correct calculation but also inserts a variance column for each individual column and I can't figure out a way to hide or disable the individual variance columns whilst leaving the subtotal v...

Filter for On or Before Yesterday
Is there any way to filter views for On or Before YESTERDAY? Right now, I'm selecting "Last X Years" but that includes today's date, which I want to exclude from the view. If you select "On or Before" and then enter yesterday's date in the calendar, that is static, so users would have to manually update the date each day, which wouldn't be a viable solution. I cannot find this functionality out of the box. Has anyone developed a solution for this? ...

correct total column in filtered list
Dear friends, I have a list(table in 2007) as bellow Serial ItemCode Import Export Remain 1 10 20 20 2 20 10 10 3 10 30 40 4 30 10 50 5 20 20 30 note how last column (remain) is calculated (remain=up remain+import-expor) if i filter (itemcode) for 10 the remain column value not calculated on new condition (correct ...

Pivot Tables #2
I am trying to format a pivot table with different rows of data. Attached is a copy of the current report, done in Crystal (it takes way to long). Is there a way to format a pivot table in Excel like this? Hi no file attached. BUT in this NG please do NOT attach files. Most people won't open them anyway. Try to explain in plain text what you're trying to achieve -- Regards Frank Kabel Frankfurt, Germany Richard E. Van Daele, Jr. wrote: > I am trying to format a pivot table with different rows of data. > Attached is a copy of the current report, done in Crystal (it tak...

Golf scores table
I have a simple database of two columns. Column 1 is the golfer's name and column 2 is the score for that round. There are about 50 golfers who have recorded 5 to 50 rounds each. I want to create a table that lists the golfers in column 1, rounds played in column 2, average score in column 3, etc. I'm going nuts trying to figure this out. Thanks. -- TimH ------------------------------------------------------------------------ TimH's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29131 View this thread: http://www.excelforum.com/showthread.php?threadid=...

count values
Hi everibody In column 1 are cells with different values (some cells have 10, other have 100 other have 500 etc); How can excel say "there are X cells with value 10, Y cells with value 100, Z cells with value 500 etc."? Thanks a lot Look in the help index for COUNTIF -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "neerav" <neerav@discussions.microsoft.com> wrote in message news:F40046DB-74B3-4F06-BC99-0853F0ECFCAB@microsoft.com... > > Hi everibody > In column 1 are cells with different values (some cells have 10, other ...

Radio Button with allowing more than 1 value to choose from
Please add the ability to have more than 2 values to choose in a radio button format, where we would add the values when creating the attribute, similar to the picklist. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Communi...

Filtering in Outlook 2003
I find it odd that whenever I use the quick option to set up a filter in Outlook 2003, it very rarely runs when the option box is ticked on closing. I nearly always have to go into the main filtering menu and uncheck 'On other machine' before it will run a filter. Am I doing something wrong? Ian No, I've had the same issue in OL2003. I've had to uncheck the 'on this machine' rule option. "Ian Pollard" <m0flc@yahoo.co.uk> wrote in message news:pv3hr2lllsvnbkqtpf9a11ovuil49v3275@4ax.com... >I find it odd that whenever I use the quick option to set ...

Tax table too big...
Upon setting the store operations up we have built a web site that goes along side it and our company has presence in 12 states, so our finance dept said we had to charge sales tax in those states by shipping zip code. This makes our tax table about 30k records or so. When the receipt at the pos is generating it takes about 45 seconds on this screen and then prints. If I delete 99% of these rows in the tax table, the rteceipt generates in a second or less. Is there a way to fix this wait time? TIA Okay fixed my own problem... It turns out it took forever on the receipt template po...