Pivot Table: use a field in two different places

M

matchorno

Two related questions:
1. Is there anyway to display a field in two different places. For example,
I want to put a date field in the page part of pivot table. However, I also
want that same date field in the row area. So when I choose "all" dates on
the page filter, I will be able to see the individual dates in the row as
well. Right now when I try to add the field, it takes it out of where it
used to be. i.e., when I add to the page, the row field disappears.

2. I want to group dates by month. But I also want to display the
individual dates in the row area right next to it. So it would look like the
following:

January 1/2/10 Joe Smith
1/5/10 Dennis Johnson
1/15/10 blah, blah, blah
February 2/1/10 Insert name here
2/3/10 another name

However I have the same problem as number 1, I can only use the date field
once.

Any help is greatly appreciated!!

Thanks!!
 
N

new1

Two related questions:
1. Is there anyway to display a field in two different places.  For example,
I want to put a date field in the page part of pivot table.  However, Ialso
want that same date field in the row area.  So when I choose "all" dates on
the page filter, I will be able to see the individual dates in the row as
well.  Right now when I try to add the field, it takes it out of where it
used to be.  i.e., when I add to the page, the row field disappears.

2. I want to group dates by month.  But I also want to display the
individual dates in the row area right next to it. So it would look like the
following:

January     1/2/10      Joe Smith
                1/5/10      Dennis Johnson
                1/15/10    blah, blah, blah
February   2/1/10      Insert name here
                2/3/10      another name

However I have the same problem as number 1, I can only use the date field
once.

Any help is greatly appreciated!!

Thanks!!

Good evening,

I suppose that you can change the data source of your pivot table.
To solve problem 1, my idea would be to add a new column to your data
with the same data as the column containing the date data.
For the 2nd problem, you can add a new column computing the month
corresponding to the date (look for the MONTH function)

Hope it helps

Regards

new1@[no/spam]realce.net
 
M

matchorno

new1@[no/spam]realce.net said:
Good evening,

I suppose that you can change the data source of your pivot table.
To solve problem 1, my idea would be to add a new column to your data
with the same data as the column containing the date data.
For the 2nd problem, you can add a new column computing the month
corresponding to the date (look for the MONTH function)

Thanks for the suggestion. I actually thought of that earlier, however, I
don't know exactly how to go about implementing that given my source data.
The reason being, I am working with a dynamic named range for my source data.
So users are constantly entering new rows of data into the source for the
pivot table. I can set up a new column to mirror the column (like you said),
however I can only do that for the existing data. When they enter new data,
that new column (for the new row they create) will not have the formula to
reference the field I want to duplicate. To fix that, i thought I could just
drag the formula all the way down the column, but when it gets past the
existing data and into the blank rows...instead of mirroring the blank row,
it gives me 1/0/1900.

Any suggestions on how to handle that? Is there a way to automatically
bring a formula down to a new row, once a user enters new data in that row?
 
M

matchorno

Thanks for the suggestion. I actually thought of that earlier, however, I
don't know exactly how to go about implementing that given my source data.
The reason being, I am working with a dynamic named range for my source data.
So users are constantly entering new rows of data into the source for the
pivot table. I can set up a new column to mirror the column (like you said),
however I can only do that for the existing data. When they enter new data,
that new column (for the new row they create) will not have the formula to
reference the field I want to duplicate. To fix that, i thought I could just
drag the formula all the way down the column, but when it gets past the
existing data and into the blank rows...instead of mirroring the blank row,
it gives me 1/0/1900.

Any suggestions on how to handle that? Is there a way to automatically
bring a formula down to a new row, once a user enters new data in that row?

I think I might have just figured out my own question. I used an If
function and then dragged that all the way down the column.
It goes: =IF(NOT((A4="")),A4,"")

That way it results in an empty string if the referenced cell is also
empty...instead of returning 1/0/1900.

Thanks!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top