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 |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
How do I force a pivot column if there is no data?
In the Field Settings for your quarter field have you checked off show items
with no data? -- HTH... Jim Thomlinson "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 |
#4
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
How do I force a pivot column if there is no data?
Are you talking about hidden rows in the actual database worksheet?
I'm reluctant to do that, because there are end users involved who might stumble upon those rows and become confused or mess things up. Also, I am not sure If I could get the result I want by adding just four rows for the four quarters. Remember I am going to be doing fairly complicated pivots with all sorts of filters. Wouldn't I need a different set of hidden rows for each pivot table? What might work is a way to programmatically add appropriate "hidden rows" when I create each pivot table. (And even then I have a problem if I am counting rather than summing rows). Or else just a way to assert at creation time that the table will contain at least a specific set of values. In article , Sandy Mann wrote: John, I had a similar problem when I wrote a Budget Spreadsheet at work. Not every Department would have an expenditure within a given month. I solved it by adding all the departments in the Department Column but in hidden Rows between the Headers and the real data. This gave me a PT Data field for every Deparment regardless of whether or not there was an entry for that Department in the Data. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "John Brock" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
How do I force a pivot column if there is no data?
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 |
#6
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
How do I force a pivot column if there is no data?
I'm reluctant to do that, because there are end users involved who
might stumble upon those rows and become confused or mess things up. You could hide them in code as xlVeryHidden which would stop end users "stumbling upon" them but of course as with all things in XL it is not foolproof against determined users. four rows for the four quarters. Remember I am going to be doing fairly complicated pivots with all sorts of filters. Of course I don't know what complicated things you want to do but if you start off with the whole database as your PT range then would it not work out anyway? -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "John Brock" wrote in message ... Are you talking about hidden rows in the actual database worksheet? I'm reluctant to do that, because there are end users involved who might stumble upon those rows and become confused or mess things up. Also, I am not sure If I could get the result I want by adding just four rows for the four quarters. Remember I am going to be doing fairly complicated pivots with all sorts of filters. Wouldn't I need a different set of hidden rows for each pivot table? What might work is a way to programmatically add appropriate "hidden rows" when I create each pivot table. (And even then I have a problem if I am counting rather than summing rows). Or else just a way to assert at creation time that the table will contain at least a specific set of values. In article , Sandy Mann wrote: John, I had a similar problem when I wrote a Budget Spreadsheet at work. Not every Department would have an expenditure within a given month. I solved it by adding all the departments in the Department Column but in hidden Rows between the Headers and the real data. This gave me a PT Data field for every Deparment regardless of whether or not there was an entry for that Department in the Data. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "John Brock" wrote in message ... 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 |
Reply |
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) |