Pivot Table with Where clause?

[Ken]

Hi all. Hope you're having a great holiday!

But back to business .

Let's say I have a worksheet with three columns: Employee, Assignment
Name, and Complete (Y/N). There is a one-to-many relationship between
Employee and Assignment Name. I understand how to make a Pivot table
to show the total number of assignments for each employee, but how
would I make a pivot table showing for each employee the number of
assignments where Complete = Y?

Thanks!

Ken

 

[Debra Dalgleish]

Add the Complete field to the Page area of the pivot table. From the
dropdown, choose Y, and the pivot table will be filtered for those
records only.

 

[Ken]

Thanks for that info.

What if I had a date column called StartDate, and I only wanted to
include items where the StartDate value was greater than or equal to
today's date? The page field drop down doesn't seem to support
something like that. Note that I am using Excel 97 SP2.

Thanks for any info again!

Ken

 

[Dave Peterson]

I'd add another column to the raw data.

Call it something nice, but put a formula like:

=if(a2>=today(),"Yes","No")

Then drag that to the page field. Then you can choose to show the yes's, no's
or both.

 

[Debra Dalgleish]

You could add a column to the table, and calculate if the StartDate
meets the criteria. For example if StartDate is in cell A2: =A2>=TODAY()

Add this new field to the PivotTable page area, and filter for TRUE.

 

[Ken]

Thanks to you and to Dave who also responded to my question. You both
came up with the same solution--and it worked like a charm.
Excel-lent (sorry about that, couldn't help myself--still must have
residue egg nog in me ).

Thanks again,

Ken

 

[Dave Peterson]

Yeah, but mine was 5 minutes earlier!!

(hehehe)

Thanks to you and to Dave who also responded to my question. You both
came up with the same solution--and it worked like a charm.
Excel-lent (sorry about that, couldn't help myself--still must have
residue egg nog in me ).

Thanks again,

Ken

 

[Debra Dalgleish]

Faster <> Better

Yeah, but mine was 5 minutes earlier!!

(hehehe)

 

[Dave Peterson]

I ain't touching that line, lady!

Faster <> Better