Using merge cells in worksheet for Pivot Table
in [General]
|
Prev: what is the formular to use from one wokesheet to a new workbook
Next: How do I add column headings to the single sheet.
From: Digno on 4 Jul 2008 21:12 I am trying to use merge cells to establish a hierarchy in a worksheet that I will turn into a Pivot Table. By hierarchy, I mean something like a Dog has fleas, bones, chew toys so if I bring up Dog on the pivot table I should get fleas, bones, chew toys to appear. In one column I put Dog then in the column beside it and below it, I put the list of sub-atributes. I merge the cells in the column under Dog till they reach the end of the list of subatributes in the column beside it. It appears like the diagram below. Dog ? M ? M ? M ? When I put the data in the pivot table, the pivot table reads the cells merged with the Dog cell as blank so the sub-attributes are paired with blanks except for the one right next to Dog. Is there someway to establish a hierarchy so that it appears in a Pivot Table? Can this be done with merge cells? -- Gracias
From: Debra Dalgleish on 5 Jul 2008 09:10
You'll have to enter the values in every row if you want them to show up in the pivot table. To hide the duplicates in the source data, you could use conditional formatting, as described here: http://www.contextures.com/xlCondFormat03.html#Duplicate Digno wrote: > I am trying to use merge cells to establish a hierarchy in a worksheet that I > will turn into a Pivot Table. By hierarchy, I mean something like a Dog has > fleas, bones, chew toys so if I bring up Dog on the pivot table I should get > fleas, bones, chew toys to appear. > > In one column I put Dog then in the column beside it and below it, I put the > list of sub-atributes. I merge the cells in the column under Dog till they > reach the end of the list of subatributes in the column beside it. It appears > like the diagram below. > > Dog ? > M ? > M ? > M ? > > When I put the data in the pivot table, the pivot table reads the cells > merged with the Dog cell as blank so the sub-attributes are paired with > blanks except for the one right next to Dog. > > Is there someway to establish a hierarchy so that it appears in a Pivot > Table? Can this be done with merge cells? -- Debra Dalgleish Contextures www.contextures.com/tiptech.html Blog: http://blog.contextures.com |