Pivot table type problem
Posted by Patrick on January 30, 2001 7:32 AM
My need is for a pivot table to produce a cell entry(a persons name) instead of the count or sum which the pivot table normally produces. In each of the data cells there will only ever be one entry. Is this possible?
A more detailed explanation follows. I have data with the following columns: date - which is only ever a Sunday, place - about 40 but which can increase (not all places are allocated each week)and person - about 50 but which can increase (not all persons are allocated each week). I need to be able to print out a pivot type chart which lists places vertically, dates horizontally and the name of the person for that week.
Posted by Mark W. on January 30, 2001 8:11 AM
Patrick, PivotTables are really a cross-tabulation and;
therefore, don't support the kind of summary that you're
seeking. Why don't you create a 2-input data table with
DGET() as the table function?
Posted by Patrick on January 30, 2001 9:23 AM
I can use DGET() but I am unsure as to your thinking behind the 2 input data table. Could you possibly explain a little more.
Many thanks
Posted by Mark W. on January 30, 2001 9:37 AM
Patrick, suppose that cells A1:C3 contain your
database:
{"Date","Place","Person"
;36892,"Austin","Larry"
;36923,"Boston","Mary"}
...and cells E1:F1 which are reserved as your
criteria range headers contain {"Date","Place"}.
You can create a 2-input table for this data set
by:
1. Enter the formula, =IF(ISERROR(DGET($A$1:$C$3,"Person",$E$1:$F$2)),"",DGET($A$1:$C$3,"Person",$E$1:$F$2)),
into cell A10.
2. Enter the text values {"Austin";"Boston"} into
cells A1:A12.
3. Enter the date values {36892,36923} into cells
B10:C10. (Note: you can format these values using
any date format you like)
4. Select cells A10:C12 and choose the Data Table...
menu command.
5. Enter E2 as the Row input cell and F2 as the
Column input cell; then press OK.
Voila! There's your table. You may want to bone
up on 2-input tables some more by consulting the
Excel Help Topics for "two-input data tables".
Posted by Mark W. on January 30, 2001 9:40 AM
Oops -- Typo!
Step 2 should read:
2. Enter the text values {"Austin";"Boston"} into
cells A11:A12.
Posted by Patrick on January 30, 2001 10:33 AM
I had already tried the Excel help files which seem to be devoid of any helpful information. Your guidance has given me sufficent info to move on to automating the process - many thanks
Posted by Patrick on January 30, 2001 10:34 AM
Re: Oops -- Typo!
Yep! I spotted that one too
