Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
How do I force a pivot column if there is no data?
Here is a simplified example of what I need to do: Let's say the
database I am pivoting includes columns (fields) for Office, Quarter, Region, and Revenue. There is a row for every Office that had Revenue in a given Quarter (Q1, Q2, Q3, Q4), but not all Offices have Revenue for all Quarters. In the Pivot Table Wizard I put Region in the Row Area, Quarter in the Column Area, and Revenue in the Data Area, so the pivot table shows Regions on the left and Quarters across the top, while summing Revenue over Offices. Couldn't be simpler, right? But here is the complication: It may happen in the database I am working with that there is no Q3 revenue for any office at all. I.e., the database contains no rows with "Q3" in the Quarter column. Nevertheless the pivot table must show a Q3 column, even though there is nothing in it. So how can I make this happen? When I pivot off of this database I only get columns for Q1, Q2, and Q4. How can I force the pivot table to include a Q3 column as well (and in the proper sequence)? As I said, this is simplified. In reality the values in the Quarter column will often change (always Q1-4, although if a Q5 shows up by mistake I want to see it), I will be doing many different pivots off of the data, and each pivot must always show all four quarters. Missing quarters will be the rule rather than the exception. Also, I will be creating these pivot tables programmatically using VBA, and I will be counting rows as well as summing. I am hoping that whatever works for my simple example will also be usable for my real-life project. Thanks in advance for any advice! -- John Brock |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
force to single column | Excel Discussion (Misc queries) | |||
How do I force a pivot column if there is no data? | Excel Discussion (Misc queries) | |||
Pivot Table- force rows with no data to be hidden | Excel Discussion (Misc queries) | |||
Pivot Table Columns force visible | Excel Programming | |||
Force pivot tables to use same source data | Excel Discussion (Misc queries) |