Pivot Table Cells

[Cajeto 63]

Hello everybody,

I'm trying to find a way to get cells values rather than numbers in the data
field of a pivot table. I only can get a count of names where I would like to
get actual names.

In order to make it easier to understand I show bellow what I mean:

What I get from a pivot table is:

Count of Nom Date
N° 3/12/2006 6/6/2006 12/3/2006 Grand Total
1 1 1
2 1 1
3 1 1
Grand Total 1 1 1 3


What I would like to get is:

Count of Nom Date
N° 3/12/2006 6/6/2006 12/3/2006 Grand Total
1 Albert 1
2 Bryan 1
3 Christina 1
Grand Total 1 1 1 3



Does anyone have a trick? It would help a lot.

Thank you.

 

[Roger Govier]

Hi

Drag the field Nom to the Row area of the PT as well as having it in the
Data Area

 

[Debra Dalgleish]

You won't be able to get the names to appear in the data area. If you
add another copy of the Name field to the pivot table, in the Row area,
you'll see the names, and a count for that name in the data area.

 

[Cajeto 63]

Hi Roger,
Thank you for your help.

If I do as you described I will get the list of names in the row field.

I would like to get the names in the data field, is there a way to do so?

Thank you.

 

[CyberTaz]

Although it's probably crystal clear to you, it's a bit cloudy on this end
not knowing what fields of data you have in the data range, how you
currently have the PT structured beyond just the Data Area, or what it is
that you're looking to glean from the PT.

Best Guess: Add the field containing the Names to the Drop Column Fields
Here Area (to the right of & in addition to what you're currently using).
Does that come close?

 

[Roger Govier]

Hi

Regrettably not.
You see the Names listed with a series of 1's against the columns that
are relevant.

 

[Cajeto 63]

Sorry if it's not clear, I'll try to explain it again:

the data I have is as follow:
N° Nom Date
1 Toto 12/3/2006
2 Albert 3/12/2006
3 Marcel 6/6/2006

And what I want to get is:
N° 3/12/2006 6/6/2006 12/3/2006
1 Toto
2 Albert
3 Christina


The data used above is only for trials purposes. And the objective is an
autorefreshed action plan to know who need to work on what for when. As you
can imagine the list of poeple, dates & numbers can be quite long...

Does it clear the clouds a little?

 

[Herbert Seidenberg]

This creates a translated table from the Pivot Table.
Add another column of numbers, unique to each person:
Nu Nom Date NomNu
1 Toto 12/03/06 3
2 Albert 03/12/06 5
3 Marcel 06/06/06 7
Name the columns with the names suggested.
Do not use N°
Create the Pivot Table with Sum of NomNu
Nu 3/12/06 6/6/06 12/3/06
1 3
2 5
3 7
Name the Data field ArrayP and create
another table with this array formula:
=IF(arrayP=NomNu,Nom,"")
The added table will look like this:
3/12/06 6/6/06 12/3/06
1 Toto
2 Albert
3 Marcel

 

[Cajeto 63]

Hi Herbert,
thank you for your help, it looks exactly like what I would like to get in
your post.

Unfortunately I must do something wrong because it does not work in my
workbook.
Instead of giving me the names it returns a "#VALUE!".

When you say "name the columns with the suggested names" do you mean the all
column or just the rows of the column containing the data?

Then when you say "Name the Data field ArrayP" if I picture it as bellow do
you mean
Arrayp=B24
or Arrayp=A14

A B C D
1 Nu 3/12/06 6/6/06 12/3/06
2 > 1 3
3 > 2 5
4 > 3 7

Thank you.

 

[Herbert Seidenberg]

When you say "name the columns with the suggested names" do you mean the all
column or just the rows of the column containing the data?

You can include rows that do not have data, in case you want to add
future data.
Just make sure all ranges (ArrayP, Nu, Nom, NuNom) have the same number
of rows.
Or use the Dynamic Range method explained by Debra Dalgleish.
http://www.contextures.com/tiptech.html

Then when you say "Name the Data field ArrayP" if I picture it as below do you mean
Arrayp=B24

The Data field is B24, the Row field is A2:A4, the Column field is
B11

another table

I meant a table located maybe at B1012, the same size as the Data
field.

with this array formula: =IF(arrayP=NomNu,Nom,"")

I meant select B1012 and enter this formula with Ctrl+Shift+Enter,
instead of just Enter.
The last item is probably the cause of your error message.

 

[Cajeto 63]

Thank you Herbert,

I don't know why but it works now.

Thank you all for your help, know I get exactly what I needed.

Regards,
Cajeto 63.