Multiple Sheet Pivot Table

[atx7953]

I've created a Pivot Table that pulls data from Multiple Sheets. Each sheet corresponds to a date so the date field is the same for each record on the sheet. The data on each sheet is in the same format and layout:


First Last Email Opened Date

Bob Jones bob@bob Yes 04/15/2012

Jane Jones jane@bob No 04/15/2012


What I want in the pivot table is to have email in the left column, Date in the row header, and the response for Opened under the data.


For example:

Date 04/15/2012 04/20/2012 04/27/2012

Email

bob@bob.com Yes No Yes

jane@jane.com Yes Yes No


The output in the Field List has produced Row, Column, Value and Page 1. By placing Column and Value in the Column area, Row in the Row area, and Page 1 in the values area I get pretty close. However, the data in each date column appears as a count. I've changes this to 'No Calculation' by changing 'Value Field Settings" but cannot seem to get it to display "Yes" or "No".


Any help in how to accomplish this is appreciated.

 

[bobhc]

Good day atx7953

An uploaded copy of the pivot would help

 

[Hui]

Atx7953

I would add another column

In that column put a formula like

=if(opened column="Yes",1,0)

Then use this new field as the Sum Value field in the Pivot Table

Then apply a Custom Number format to the PT data area

Use a custom number format [>0]"Yes";[=0]"No"


As all your worksheets are in the same format you can group them and add the extra field quite easily