pivot table multiple criteria

Hi,

I have a pivot table in which I can select a weeknumber to get the number of 
visitors in a pivot table and diagram.
I'm looking for a possibility to select multiple weeknumbers and get the 
combined result in a pivot table and diagram. I was thinking of a kind of 
selection buttons or something like that.

Any help or suggestion is more than welcome.

Kind regards,
Chris 


0
cclaes (14)
10/6/2008 4:36:42 PM
excel 39879 articles. 2 followers. Follow

2 Replies
261 Views

Similar Articles

[PageSpeed] 15

Hi Chris

I am assuming you are working with XL2003 or earlier.
Is your field a Page field?
If so, drag it to the Row area, make your Multiple selections and drag back 
to the Page area where the selection will show as "Multiple Items"

-- 
Regards
Roger Govier

"Chris" <cclaes@telenet.be> wrote in message 
news:s8rGk.35374$qg2.7615@newsfe30.ams2...
> Hi,
>
> I have a pivot table in which I can select a weeknumber to get the number 
> of visitors in a pivot table and diagram.
> I'm looking for a possibility to select multiple weeknumbers and get the 
> combined result in a pivot table and diagram. I was thinking of a kind of 
> selection buttons or something like that.
>
> Any help or suggestion is more than welcome.
>
> Kind regards,
> Chris
> 
0
Roger
10/6/2008 5:29:00 PM
Hi Roger,

this does indeed work, but the problem is that the pivot table is generated 
after selecting a button with a macro. The people using the file are simply 
using it without being able to intervene in the pivot table. So what I am 
really looking for is a possibility to select different week numbers in the 
dropdown list where you can select the week and thus generating a pivot 
table based on the selected week numbers.

I will include hereby my macro script, maybe that will help.

Sub Pivot()
'
'   Aanmaak draaitabel voor bezoekersaantallen per bezoekerstype
'
'   Controle of cel A2 op werkblad Totaal ingevuld is
    Sheets("Totaal").Select
    If IsEmpty(Range("A2")) Then
        Range("A2").Select
        MsgBox "Er zijn geen gegevens beschikbaar!", vbInformation, 
"OPGELET"
    Else
    Dim laatsterij As Integer

    Sheets("Pivot").Select
    Cells.Select
    Selection.Clear
    Range("A1").Select
    Sheets("Totaal").Select
    ' bepalen wat de laatste rij is
    Range("A65536").Select
    Selection.End(xlUp).Select
    laatsterij = ActiveCell.Row
    'MsgBox laatsterij

    Rows("1:" & laatsterij).Select

    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "Totaal!R1C1:R" & laatsterij & "C14").CreatePivotTable 
TableDestination:= _
        "'Pivot'!R1C1", TableName:="PivotTable5", _
        DefaultVersion:=xlPivotTableVersion10
    Sheets("Pivot").Select
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("WEEK")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("VERKOPER")
        .Orientation = xlPageField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable5").AddDataField 
ActiveSheet.PivotTables( _
        "PivotTable5").PivotFields("CM"), "Count of CM", xlCount
    ActiveSheet.PivotTables("PivotTable5").AddDataField 
ActiveSheet.PivotTables( _
        "PivotTable5").PivotFields("P. AANN."), "Count of P. AANN.", xlCount
    ActiveSheet.PivotTables("PivotTable5").AddDataField 
ActiveSheet.PivotTables( _
        "PivotTable5").PivotFields("P. ARCH."), "Count of P. ARCH.", xlCount
    ActiveSheet.PivotTables("PivotTable5").AddDataField 
ActiveSheet.PivotTables( _
        "PivotTable5").PivotFields("PART."), "Count of PART.", xlCount
    MaakGrafiek
    Application.CommandBars("PivotTable").Visible = False
    ActiveWorkbook.ShowPivotTableFieldList = False
    End If
End Sub

As I am dutch speaking, there may be some strange words for you in the vba 
script.

Regards,
Chris


"Roger Govier" <roger@technology4unospamdotcodotuk> schreef in bericht 
news:eKsNKk9JJHA.728@TK2MSFTNGP03.phx.gbl...
> Hi Chris
>
> I am assuming you are working with XL2003 or earlier.
> Is your field a Page field?
> If so, drag it to the Row area, make your Multiple selections and drag 
> back to the Page area where the selection will show as "Multiple Items"
>
> -- 
> Regards
> Roger Govier
>
> "Chris" <cclaes@telenet.be> wrote in message 
> news:s8rGk.35374$qg2.7615@newsfe30.ams2...
>> Hi,
>>
>> I have a pivot table in which I can select a weeknumber to get the number 
>> of visitors in a pivot table and diagram.
>> I'm looking for a possibility to select multiple weeknumbers and get the 
>> combined result in a pivot table and diagram. I was thinking of a kind of 
>> selection buttons or something like that.
>>
>> Any help or suggestion is more than welcome.
>>
>> Kind regards,
>> Chris
>> 


0
cclaes (14)
10/6/2008 8:33:14 PM
Reply:

Similar Artilces:

Allowing both multiple values and list edits in Lookup field
In Access 2007 I am trying to make a table column utilize a combo box that allows both multiple values and list edits. However, when I set it to allow both features, it doesn't let me make any edits to the list in the table; the multiple values aspect works just fine. When I don't allow multiple values, I can edit it to my heart's content. Anyone know a way around this problem? I know that I'm doing it right (all you have to do is select "Yes"!) but it's jut not allowing me to have both allowances as it should. Jess If you are saying that you a...

Chart type not remembered in a pivot chart
if you change a dataseries chart type e.g to line from bar within a pivot chart, it reverts back to it’s original setting when the chart is refreshed !! Surely this cannot be right? tried in Office 2000,2002,2003 & 2007beta ---------------- 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 mess...

Receive one message to multiple user mailboxes
Hello all, Does anyone know if there is a way to have a single message routed to multiple users? When they had an ISP handling the mail, they just set Outlook to save the messages on the server, and had multiple users pull mail from that box. Now with Exchange Server, they want a message sent to admin@domain.com to drop into 2 or 3 different user mailboxes. Is this possible? or do they have to setup a common mailbox and give the users access to it? or other suggestions?? Thank you in advance. you need only to configure a distribution group with the smtp address admin@domain.com, ...

multiple program windows?
How can I enable a second excel program window to view on a secon monitor connected to a laptop -- tafstewar ----------------------------------------------------------------------- tafsteward's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2593 View this thread: http://www.excelforum.com/showthread.php?threadid=39304 You would use the same Excel instance, but drag the right border of Excel to run into the next display. If it cannot be done with just the graphics cards, you could download and try the stuff from www.ultramon.com. It will do it for sure, ...

More than one Table
I have an Old table TABLE 1 in an application which has a check fields CHK1, CHK2 in it. I have recently upgraded the application so that the check field are now a series of records in a sub-form TABLE 2. QUESTION I want to increment through TABLE 1 one record at a time, then run a conditional statement to see if CHK1, CHK2 is yes in each respective check field and if it is I want to open TABLE 2 and add record to TABLE 2 so that reflects the respective CHKn. My question is can 2 recordsets be open simultaneously and if so how do I swap between the 2 so that my VBA applic...

Pivot: Selecting data with Query Result
Hello Community, I am using Pivot functunality to get access to data that is stored in a classical table. As I result from selecting the name in the the page field, I would to display the corresponding values like customer number and post code just below the name field but not in the data field. Moreover, I want to be able to enter the customer number once and another time the name, whereas each time the "related field" is filled correspondingly. Could anybody help me on that issue. Thanks and cheers. Glenn Format of the source data: #Name #Customer Nr. # Postcode # Reven...

Delete Rows from Data Table
I used the dummy columns method to create a 2 axes column chart and it worked great! However, I want to be able to show the data table with the chart, but I don't want the 2 dummy columns to show up on the table. I try right- clicking on the chart to delete those 2 rows that I don't want from the table, but it doesn't allow me to clear it. Any ideas? Data tables are hard to work with. You have limited formatting options, and you can't have data in the chart that doesn't show up in the data table. You could make your own data table in the worksheet range under the ...

Importing Journal Entries Using Table Import
Hello I am doing some research to see which methods would work best for importing journal entries created in a third-party application into Great Plains. So far I've completed the process using the integration manager and now I am researching the table import method. The table import process is causing me some problems because I cannot get the account numbers to import. There is a typo in the GL Transacton Entry SDK document I have, so I am trying to work around it. I am not an Engineering, my background is more of end-user. If someone has some suggestions on how to map to the ac...

how to rotate excel table in word
I insert a excel worksheet in word and like to rotate it Select the area in the worksheet you want to move to Word. While hold down the shift key, pull-down Edit > Copy Picture. In Word, just paste. Because the item is now a picture, you can use the drawing toolbar to rotate it to any desired angle -- Gary''s Student "dro" wrote: > I insert a excel worksheet in word and like to rotate it Thanks, its working just fine for me. "Gary''s Student" wrote: > Select the area in the worksheet you want to move to Word. While hold down > the ...

Producing combined table grouped by categories
Hi! I have two tables: Categories and Expenses. Categories table has 3 fields: CategoryID CategoryName CategoryDescription 1 Stationaries Office materials, pens, paper, etc. 2 Petrol Petrol for company vehicles 3 Vegetables Expenses related to vegetables 4 Fruits Expenses related to fruits 5 Utilities Gas, electricity, water, etc Expenses table has 4 fields: ExpenseID CategoryID ExpenseName AmmountSpent 4 ...

How to handle Outlook Contacts who have multiple Names
We have many contacts who have multiple names, e.g., (1) women who, having been married several times, sometimes use their "maiden" surname, and sometimes use their first or second husband's surname; (2) people who sometimes use their formal name, and sometimes use a nickname [e.g., Jack Kennedy]; (3) people who live in Israel, who sometimes use their "English" names [e.g., Jeffrey Goldberg], but also use entirely different names in Hebrew [e.g., Yakov Ben-Tzion]. Is it possible to create (what I'll call) "Shortcuts" in Outlook (for a part...

VB reference to table field
I've got this code, but I need to figure out the syntax for the !COMMENT part. I want it to refer to the master table's COMMENT field. This code, however is in the main form. I've tried [Tables]!Table1.Comment among others, but it gives an error. Any help? Thanks!!! With Me.RecordsetClone .AddNew !Name = Me.Name !DOB = Me.DOB !COMMENT = .Update "Mark1" <Mark1@discussions.microsoft.com> wrote > I've got this code, but I need to figure out the syntax for the !COMMENT ...

Copy to a Word Table
Hi I am trying to write some vba code in Excel 2007 to paste a range into a Word (either 2003 or 2007) table. My code so far, selects the correct table and selects the top (blank) row in the table. I cannot get the next part correct. I need to: 1. Insert the correct number of rows in the Word table to accommodate the Excel data. I have used noRows = Selection.Rows.Count 2. Select those rows that have been inserted and paste the data to these rows. When doing this manually it seems to produce the best results, rather than paste as a nested table etc. Any suggestions of the corre...

Displaying Multiple Text Lines At Runtime
I need to display various text messages in my dialog app, and they change during the application. There will be multiple lines of text in one rectangular area. In order to handle that and format it well, should I not use the edit box with an attached CStatic variable? Should I just use text out with coordinates and maybe draw a rectangular area instead of using a control? Thanks, Matt There are several ways by which you can acheive this. 1. In your Status bars, create a new panel for displaying your messages. 2. Create a control bar, which can be docked/closed, and write your messages in...

Import multiple photos into excel
I am trying to create a photo sheet that will allow me to import all photos in a directory into an excel spreadsheet. I want it to: Import all images in a directory. (even if I have to choose to open the folder and then select all) Place each photo into a separate box Auto-format all the photos to the same size where (2) images fit on each printed page. Have a space for a text description to the right of each photo. Auto-number each row/photo if possible (so it changes all the numbers if I delete a row) Place a header with a logo on each printed page. Insert a merge field in t...

Multiple CRM Sites (child domains) on 1 Server
I would like to install multiple CRM sites, from different child domains, on 1 CRM server. I believe this is possible. Are there any things I should be aware of? Any critical issues that will arise? Is this covered in the implimentation guide....off to get it now. Thanks. "Scott" <wikayaker@gmail.com> wrote in message news:1141061562.354599.267210@u72g2000cwu.googlegroups.com... >I would like to install multiple CRM sites, from different child > domains, on 1 CRM server. Not possible, sorry. I believe I read MS is considering this for the next version, to f...

Multiple copies of the same email downloaded to inbox
I have checked the 'keep copy on server' since I download to multple machines. However, randomly any machine will continue to download not only new messages but whatever is in the pop box on the server (i.e. non deleted email). this of course is extremely annoying. Anybody have any idea of what might be wrong? Is it a server problem or a client problem. I tend to think it might be server since it happens randomly on any client. Thank, Greg What version of Outlook do you have? -- Jeff Stephenson Outlook Development This posting is provided "AS IS" with no warranties...

Basic Pivot Table Question (using 97)
Prior to a week ago I had never heard the term "Pivot Table." I am now working on building a fairly complex workbook and I am thinking that maybe some of what I am doing might be best accomplished using one, but I don't actually understand what it is, or what it does. The various lookup functions just don't seem to be strong/flexible enough for what I am doing. Would someone please explain in "plain english" what a pivot table is and what it does/allows you to do. All the information I have seen seem to assume the possession of this basic knowledge, an...

multiple selection
how to have the multiple selection in the interfaces of research of the recordings in MS CRM 3.0 because a message is posted: "you can select 1 at the same time line" ...

OE6 Multiple identities receiving mail on shared computer
In Outlook Express 6, we have several identities sharing one computer. All identities receive all emails directed at any one of the identities. Does anyone know what causes this problem? Please Advise of solution. Post in an Outlook Express group - this is not one of them. Outlook is a part of Microsoft Office - Outlook Express is a part of Internet Explorer. You can also try http://insideoe.tomsterdam.com for some excellent OE assistance. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus,...

Pivot table numbers turn into dates on refresh
This mystery occurrance has happened to me before but I can't remember how to correct it. Any one with similar experience and a solution please? Visit www.ExcelGoodies.Com...

howto setup SMTP Connectors to single/multiple smarthosts?
Have multiple W2K user machine (minimum SP4) and Outlook 2003 (SP1) that connects to a new SBS 2003 (SP1). All e-mail is via "smarthost". Attempting to set Exchange up to initially send (SMTP) to a smarthost that requires a different "Outbound Security" setting for each email user. Later, want to be able to send to any smarthost based on the email that the user set up (different smarthost/name/password). Currently have POP3 Connector bringing in the mail from multiple smarthosts/name/password just fine. The SMTP Virtual Server has no "Smart Host" or &quo...

no show all function in dropdown list in Pivot Table
Dear Sir, I'm using excel 2000 professional version. When I create new pivot table, everthing is fine but once I want to look at the specific item, I have no "show all" function in the dropdown list of item field. So that I have to unmark all items, instead of only unmark the "show all". How can I have "show all" function in this case? thanks In Excel 2000, and earlier versions, the Show All checkbox isn't available. You can use programming to hide or show the items -- there's sample code here: http://www.contextures.com/xlPivot03.html...

WORD: Table of Contents with latin numeration
If have got a document dividedin sections. The first sections use latin page numbering and the rest normal arabic numbering. For each section this is working all fine. The only trouble appears when I create the TOC. The reference to the page numbers of those sections that use latin page numbering appears with arabic numbering. THe example will surely clarify the problem: It appears as ****************** Table of contents..................................2 Foreword ...................................4 Summary ...........................................5 1 Introduction ......................

typeing in a Form and automatically writing to a Table
Good day all, I have a question that is puzzling me and need Help. I have a form that I am using to track workorders, as I enter the work order, I have it designed to send an e-mail. the problem is that when I type in the form it doesn't automatically write to the table. the Query that is triggered once the Button is selected, cannot ID the added fields until after the fact. Can someone please shed some light? -- -The Novice Learn Today, Teach Tomorrow Great Success is ones ability to ask for Help. =?Utf-8?B?VGhlTm92aWNl?= <TheNovice@discussions.microsoft.com> wrote in ne...