I can see in Google Groups that Jim Thomlinson has suggested that
I try the "Show items with no data" checkbox. For some reason
though his post hasn't shown up in my newsreader yet, so I can't
respond directly.

It doesn't work though. That checkbox is only for when you are
using a page field, and some of your pages have data for a column
while others don't. In my case though the string "Q3" never appears
anywhere in the database, so there is no way Excel can guess what
I want. (How would it know I didn't really want "Q3.5")?

Or to look at it another way, what if I wanted a region called
"Antarctica" to show up in every pivot table, even though no rows
in the database are ever listed as being in that region? There is
obviously no way I could do that using "Show items with no data"
-- somewhere I am going to have to explicitly supply the name!

In article ,
John Brock wrote:
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




--
John Brock