PivotTable Help

RoyinUK2000 · Apr 30th, 2002, 11:38 AM

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

RoyinUK2000 · May 1st, 2002, 11:35 AM

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

Thx
Roy

EdE · May 1st, 2002, 12:16 PM

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.

RET79 · May 1st, 2002, 12:30 PM

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

RoyinUK2000 · May 2nd, 2002, 12:34 AM

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

RoyinUK2000 · May 2nd, 2002, 10:44 AM

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

Thx
Roy

RoyinUK2000 · May 2nd, 2002, 01:28 PM

^^bump^^

RET79 · May 2nd, 2002, 01:40 PM

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

RET79 · May 2nd, 2002, 01:56 PM

Roy,

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

RET79

RET79 · May 2nd, 2002, 02:17 PM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 30th, 2002, 11:38 AM	#1
RoyinUK2000 Board Regular Join Date: Mar 2002 Posts: 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

May 1st, 2002, 11:35 AM	#2
RoyinUK2000 Board Regular Join Date: Mar 2002 Posts: 79	Come on guys ......No replies. I'm sure someone somewhere has a suggestion or some help for me. Thx Roy

May 1st, 2002, 12:16 PM	#3
EdE Board Regular Join Date: Apr 2002 Location: A Mile High!! Posts: 230	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.

May 1st, 2002, 12:30 PM	#4
RET79 Board Regular Join Date: Mar 2002 Location: England, UK. Posts: 526	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

May 2nd, 2002, 12:34 AM	#5
RoyinUK2000 Board Regular Join Date: Mar 2002 Posts: 79	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

May 2nd, 2002, 10:44 AM	#6
RoyinUK2000 Board Regular Join Date: Mar 2002 Posts: 79	Can some one give me an explanation of how the above are incorporated into a pivot table please Thx Roy

May 2nd, 2002, 01:28 PM	#7
RoyinUK2000 Board Regular Join Date: Mar 2002 Posts: 79	^^bump^^

May 2nd, 2002, 01:40 PM	#8
RET79 Board Regular Join Date: Mar 2002 Location: England, UK. Posts: 526	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

May 2nd, 2002, 01:56 PM	#9
RET79 Board Regular Join Date: Mar 2002 Location: England, UK. Posts: 526	Roy, I tried doing that and it looks ok, let me know if you have had any success. RET79

May 2nd, 2002, 02:17 PM	#10
RET79 Board Regular Join Date: Mar 2002 Location: England, UK. Posts: 526	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