MrExcel Publishing
Your One Stop for Excel Tips & Solutions

show pivot item for every row


Posted by Tom on March 06, 2001 3:56 PM

I've got a pivot table that generates data in a format that totals in the following manner:

Product Month Total

Both product and month are column fields, but displays for each product, as the hierarchy dictates.

I need the product number to show up in each row, as does the Month and Total already. Any ideas?

Thanks much


Posted by Mark W. on March 06, 2001 4:38 PM

> I need the product number to show up in each row, as does the Month and Total already.

Why?

Posted by Bruce on March 07, 2001 12:12 PM

Mr. Excel has a tip on how to do this.
http://www.mrexcel.com/
then look for this tip:
12/12/98: Use Goto - Special - Blanks to clean up a pivot table after it has been changed to values. Updated 9/26/99


Posted by Mark W. on March 07, 2001 1:48 PM

Bruce, of course, if you use this tip you won't
have an update-able PivotTable anymore. The
reason I asked, "Why?", is that I suspect that
Tom is trying to do a lookup against this
PivotTable and isn't familiar with the
GETPIVOTDATA() function. Sometimes it's
necessary to ask a few question to get to the
crux of a problem.

Posted by Bruce on March 08, 2001 6:34 AM

You are right Mark.

I wanted to do something similar to what Tom is asking and used a function that worked for me that still allowed me to keep the update-able pivot table. In my example, the first column "A" is "sites", the second column "B" is "week". Only the first instance of every site is present in the pivot table. There are no blank "week" cells since each week only occurs one time per site. Assuming my first "site" is in "A5", I go to a column outside of the pivot table, say "P5", and enter =IF(ISBLANK(A5),P4,A5), and copy down the column. Why do I do this? Because each site can have up to 52 "week" rows at year end, it allows my viewers to quickly see which site they are looking at, especially when they are viewing the final grand totals column of the pivot table, and my function is in the first column outside of the pivot table. There may be an easier way, but this did work for me.