Preserve rows in pivot table when refreshing data

[LarryJB]

<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 9"><meta name="Originator" content="Microsoft Word 9"><link rel="File-List" href="file:///C:/DOCUME%7E1/LAWREN%7E1/LOCALS%7E1/Temp/msoclip1/01/clip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:DoNotOptimizeForBrowser/> </w:WordDocument> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} h3 {margin-right:0in; mso-margin-top-alt:auto; mso-margin-bottom-alt:auto; margin-left:0in; mso-pagination:widow-orphan; mso-outline-level:3; font-size:13.5pt; font-family:"Times New Roman"; font-weight:bold;} a:link, span.MsoHyperlink {color:blue; text-decoration:underline; text-underline:single;} a:visited, span.MsoHyperlinkFollowed {colorurple; text-decoration:underline; text-underline:single;} p {margin-right:0in; mso-margin-top-alt:auto; mso-margin-bottom-alt:auto; margin-left:0in; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style> Say I have a Pivot Table (Excel 2000) with two columns. The field, “Region,” is in the leftmost column and it contains two levels, “A” and “B,” which are displayed as individual rows. The next column (the Data field of the Pivot Table) shows counts for each level of Region. Counts for both regions are greater than zero. Then I change the source data by deleting all the Region B data. When I refresh the pivot table, the row showing Region B counts simply disappears.



However, I would like the pivot table to continue showing a row for Region B that has a count of “0.” By the way, in Field Settings for the Region field, I have checked the box to “Show items with no data.” Also, in Table Options I have checked “Preserve formatting.”
<!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
I’ve done an extensive search for a way to do this but have been unsuccessful so far. I’d be very grateful for any help with this problem.

 

[Fazza]

Hi, Larry.

One way would be to join two tables of data as the dataset for the pivot table. One table with your actual data and a second table just listing the Regions so that they are in the dataset and will list in the pivot table.

Such as, say you give the current source data the defined name "source". Not a dynamic named range.

And create a separate table named "regions" with header region and under it lists A & B each in their own cell.

I'll assume these are in the one workbook (though they need not be). Close the file. Open a new file and go via menu data, import external data, new database query. Excel files. OK. Your Excel file. OK. When you see the defined name source select all the fields from the LHS to the RHS. Continue a few steps and when you get to finish take the option to edit in MS Query. Once there edit the SQL - hit the 'SQL' button - to be like below, it is just text. I have assumed a data field called value.

SELECT source.region, source.value
FROM source
UNION ALL
SELECT regions.region, Null AS [value]
FROM regions

Hit the SQL button to enter. OK to any message about not being able to graphically show whatever. You will see the dataset. Hit the 'open door' icon to exit MS Query and take the pivot table option.

HTH, Fazza

 

[Fazza]

PS

The pivot table was created in a new workbook to avoid memory leak problems. Once created, it can be moved into the original workbook if you like.

F

 

[LarryJB]

Thank you, Fazza. I apologize for the tardiness of this "thank you." I'm new to this site, and thought I would get an email that someone had replied. Glad I checked the site instead of waiting for that email. Thanks again.

 

[Fazza]