Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: PivotTable Help

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    79
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a spreadsheet with data on it for the past six months. I am looking at the data with a pivot table. Is there any way I can get the pivot table to only dispaly data between two dates eg 10th Feb to 12th April. I would like these dates to be entered by a user then the pivot table adjusts accordingly. Any guru's with any suggestions!!!

    Thx
    Roy

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Posts
    79
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Come on guys ......No replies.
    I'm sure someone somewhere has a suggestion or some help for me.

    Thx
    Roy

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    A Mile High!!
    Posts
    241
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Roy --

    I had a similar situation and couldnt figure out how to do it without teaching user how to use pivot tables. I used a work around and used a macro with Advanced filter, input boxes, and drop down menus to accomplish what it sounds like you are doing. Let me know if youre interested in it.

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I know you can display groups in pivot tables, but not done it myself.

    Perhaps in your case you could try and get

    x<12Feb
    12Feb<=x<=10March
    x>10Match

    Sorry, I never done this before but know you can group data like that in a pivot table if you make the right moves. Will try and find out for you but no promises.

    RET79

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    79
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ede,
    Yeah I am Definitely interested, can you post an example of what u did or mail it to me

    roy.brunt@stepanuk.com

    Thx
    Roy

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Posts
    79
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Can some one give me an explanation of how the above are incorporated into a pivot table please

    Thx
    Roy

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Posts
    79
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ^^bump^^

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Roy,

    You need to learn how to group pivot table items.

    I have not even tried this, but am quoting you from a reference book what u should do:

    Select one of your row fields of yoru pivot table. Choose Data-Group and outline - group from the toolbar meny. There you get a box popping up saying where you want to start and end the grouping etc.

    This should be a starting point. If this feature works with dates then why dont you record yoru actions with a macro recorder and see what code u get and then see what you can do.

    Try and play with this first,

    RET79

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Roy,

    I tried doing that and it looks ok, let me know if you have had any success.

    RET79

  10. #10
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    So you record a macro of yourself creating a pivot table. This is what I did - I put some dates from 1st Feb 2002 every day down to 22 Feb 2002 from A2 down, then put some data next to it in column B. Then create a pivot table using the wizard, putting date as a row field, sum of data col B as data field.

    Then, once you have this pivot table, go to Data - > group ad outline - > group and put the dates 5/2/2002 for start and 10/2/2002 as end. This generates a macro code like this:

    Selection.Group Start:=37299, End:=37302, Periods:=Array(False, False, _
    False, False, True, False, False)

    but here start and end are numbers, maybe someone else can help you here.

    HTH

    RET79

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •