Pivot Table question

[erick-flores]

Hello all

I am creating this Pivot Table in Excel2003. I have multiple fields:
employees, itemamount, itememountco., expensecategory, department and
period ending. My PivotTable looks perfect but I want to be able to
sort/arrange my data by period ending. In other words be able to set a
range based on the data behind the period ending field. Something where
I can input "10/1/2006 - 11/15/2006" so my PivotTable will display ONLY
data under that range.

Any ideas?

Thanks in advance

 

[Guest]

Can you not include a date field in your table, and then select the dates you
want from there?

 

[erick-flores]

Can you not include a date field in your table, and then select the dates you
want from there?

I am sure can, but the president of the company wont like that, he
wants a better/easy way to do this. Like just have a field in the excel
spreedshet or maybe a pop up where he can write the dates and get the
data right away. You know what I mean?

I know how to do this in Access but in Excel I dont know how to, any
ideas?

 

[Debra Dalgleish]

You could add a field to the source data, to calculate if the record
should be shown in the pivot table. For example, with dates in column A:

=AND(A2>=StartDate,A2<=EndDate)

StartDate and EndDate are named ranges, where the date range could be
manually entered or calculated.

In the pivot table, add the new field to the Page area, and select TRUE
from its dropdown list.

 

[erick-flores]

In the pivot table, add the new field to the Page area, and select TRUE
from its dropdown list.

Thanks for your reply, but let me see if I get this right
1. > You could add a field to the source data, to calculate if the
record

should be shown in the pivot table.

So I need to add a new field to my table, right? with no data on it,
just an empty field. And then add this field to the DATA part in my
PivotTable.

2. =AND(A2>=StartDate,A2<=EndDate)
Where do I put this function? in the field that would be in the DATA ?
how do I write a function in there?

3. > StartDate and EndDate are named ranges, where the date range could
be

manually entered or calculated.

Need to have two cells where i would be putting my StartDate and
EndDate, I understand that. But how would this be related to my field
that has the period ending?

I am kinda lost in here, if you could please guide me here
step-by-step, Thank you very much

 

[erick-flores]

I got it working, did it other way around but works perfect

 

[Debra Dalgleish]

Great! Thanks for letting me know that you got it working.