PivotTable Help

RoyinUK2000

Board Regular
Joined
Mar 26, 2002
Messages
79
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Come on guys ......No replies.
I'm sure someone somewhere has a suggestion or some help for me.

Thx
Roy
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Can some one give me an explanation of how the above are incorporated into a pivot table please

Thx
Roy
 
Upvote 0
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
 
Upvote 0
Roy,

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

RET79
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top