pivot table multiple consolidation ranges

When I create a pivot table based on data from one excel worksheet, the pivot 
table automatically creates one layout field for every column header on the 
worksheet; but when I choose the multiple consolidation option and add two 
consolidation ranges to my pivot table data, the layout shows only a field 
named "Rows" and one named "Columns" - in other words, it doesn't create 
fields named after the column headers that I can drag into the respective 
pivot layout areas (page, column, row, data). 

The column headers in both of the worksheets (consolidation ranges) are 
exactly the same, and the datatypes in the respective columns are also 
exactly the same in both worksheets.

What am I missing?

Thanks for your help ...

Bill 
0
10/26/2004 12:53:03 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
544 Views

Similar Articles

[PageSpeed] 9

You aren't missing anything -- that's a limitation of Pivot Tables 
created from multiple consolidation ranges. There's an example here:

       http://www.contextures.com/xlPivot08.html

If possible, store your data in a database, or on one worksheet, and
you'll have more flexibility in creating the pivot table.


bill_morgan_3333 wrote:
> When I create a pivot table based on data from one excel worksheet, the pivot 
> table automatically creates one layout field for every column header on the 
> worksheet; but when I choose the multiple consolidation option and add two 
> consolidation ranges to my pivot table data, the layout shows only a field 
> named "Rows" and one named "Columns" - in other words, it doesn't create 
> fields named after the column headers that I can drag into the respective 
> pivot layout areas (page, column, row, data). 
> 
> The column headers in both of the worksheets (consolidation ranges) are 
> exactly the same, and the datatypes in the respective columns are also 
> exactly the same in both worksheets.
> 
> What am I missing?
> 
> Thanks for your help ...
> 
> Bill 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
10/26/2004 12:59:53 AM
Debra,

Thanks for the clarification. I could have spent a long time trying to 
figure that one out! 

Bill
 

"Debra Dalgleish" wrote:

> You aren't missing anything -- that's a limitation of Pivot Tables 
> created from multiple consolidation ranges. There's an example here:
> 
>        http://www.contextures.com/xlPivot08.html
> 
> If possible, store your data in a database, or on one worksheet, and
> you'll have more flexibility in creating the pivot table.
> 
> 
> bill_morgan_3333 wrote:
> > When I create a pivot table based on data from one excel worksheet, the pivot 
> > table automatically creates one layout field for every column header on the 
> > worksheet; but when I choose the multiple consolidation option and add two 
> > consolidation ranges to my pivot table data, the layout shows only a field 
> > named "Rows" and one named "Columns" - in other words, it doesn't create 
> > fields named after the column headers that I can drag into the respective 
> > pivot layout areas (page, column, row, data). 
> > 
> > The column headers in both of the worksheets (consolidation ranges) are 
> > exactly the same, and the datatypes in the respective columns are also 
> > exactly the same in both worksheets.
> > 
> > What am I missing?
> > 
> > Thanks for your help ...
> > 
> > Bill 
> 
> 
> -- 
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
> 
> 
0
10/26/2004 1:15:06 AM
You're welcome!

bill_morgan_3333 wrote:
> Debra,
> 
> Thanks for the clarification. I could have spent a long time trying to 
> figure that one out! 
> 
> Bill
>  
> 
> "Debra Dalgleish" wrote:
> 
> 
>>You aren't missing anything -- that's a limitation of Pivot Tables 
>>created from multiple consolidation ranges. There's an example here:
>>
>>       http://www.contextures.com/xlPivot08.html
>>
>>If possible, store your data in a database, or on one worksheet, and
>>you'll have more flexibility in creating the pivot table.
>>
>>
>>bill_morgan_3333 wrote:
>>
>>>When I create a pivot table based on data from one excel worksheet, the pivot 
>>>table automatically creates one layout field for every column header on the 
>>>worksheet; but when I choose the multiple consolidation option and add two 
>>>consolidation ranges to my pivot table data, the layout shows only a field 
>>>named "Rows" and one named "Columns" - in other words, it doesn't create 
>>>fields named after the column headers that I can drag into the respective 
>>>pivot layout areas (page, column, row, data). 
>>>
>>>The column headers in both of the worksheets (consolidation ranges) are 
>>>exactly the same, and the datatypes in the respective columns are also 
>>>exactly the same in both worksheets.
>>>
>>>What am I missing?
>>>
>>>Thanks for your help ...
>>>
>>>Bill 
>>
>>
>>-- 
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>>
>>
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
10/26/2004 1:20:02 AM
Reply:

Similar Artilces:

I need to concatenate multiple Publisher/PDF files into 1 PDF document
Help! I'm working on policy/procedure documents and I'm trying to concatenate all document sections into 1 PDF file. Is this possible? If 'yes' how? In Adobe Acrobat, file, Create PDF... from Multiple files... -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com <rodney.ingram@sdhc.k12.fl.us> wrote in message news:1136562593.047928.259530@f14g2000cwb.googlegroups.com... > Help! > > I'm working on policy/procedure documents and I'm trying to > concatenate all document sections into 1 PDF file. Is ...

how to combine each record into a range?
how to combine each record into a range? Table1 Day Value 1 0 2 0 3 0 4 1 5 1 6 1 7 0 8 0 9 0 10 0 11 1 12 1 13 0 14 1 15 0 16 0 17 1 18 1 19 0 20 1 .. . .. . .. . 1000 1 how to covert the above table into: DayStart DayEnd Value 1 3 0 4 6 1 7 10 0 11 12 1 13 13 0 14 14 1 15 16 0 17 ...

need to take out an INNER JOIN to a user table
I am trying to edit the following query. When I open it, Access really mangles it so that it doesn't even work. This goes to a form in the database and there are drop down menus for choices. I'm trying to add the parameters in those lists to run the query. The first time I ran it it asked for the first parameter I think was curUser. Now it doesn't ask for that anymore but it does ask for the form drop down fields. I will try inputing a value in the select query. The problem is I want the inner join taken out so Access won't keep destroying the query. It puts pa...

Multiple e-mail addresses #4
I have three different e-mail addresses, one each for personal, business, and school and would like to know if it is possible through MS Outlook to have the messages sent to the personal and school addresses automatically forwarded to my business address. Also, can I copy my calendar from my home PC to my work PC through e-mail. Thanks very much for any help. Chris, I have been wondering about this for some time, and reading your post made the penny drop: you could create a rule to automatically forward email from one account to another. It's an easy rule to create (Tools | Rules...

Update cell based on date range
Hey guys! I posted this in the General Forum, but I figured this woul be a better place for this question. I was wondering if I could ge some help here. I would like to update a cell based on a date range For example, I would like to update the value of a cell to the value o another cell if the current date is between July 1st and July 10th However, if the date is outside the date range, I want the value fo that cell to not be updated, and be the previous value. Can anyone giv me an example as to how I would do this? Thanks!! -- deversol -----------------------------------------------------...

Multiple lookup values in =HLOOKUP
A classical formula might be =HLOOKUP(a1,a100:z100,1,FALSE). But I want to display the result if the lookup value = a1 or a2 or a3 or...a10 ie: anything in the range a1.a10, without writing a multiple nested statement. Any ideas? Thanks. -- Peter London, UK Try... =INDEX(A1:A10,MATCH(TRUE,INDEX(COUNTIF(A100:Z100,A1:A10)>0,0),0)) ....confirmed with just ENTER, or... =INDEX(A1:A10,MATCH(TRUE,COUNTIF(A100:Z100,A1:A10)>0,0)) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article <CE55088A-5E9F-4C94-8D91-4F4AD66E63A7@microsoft.com>, "Peter&q...

Creating more than one pivot table in one page?
Creating more than one pivot table in one page? how do i do that? Pls help Hi Kumar quite simple (at least in Excel 2003): - create your first pivot table - select an empty cell below or right to the first one - start the pivot table assistant - as target choose the location below/right to the existing table -- Regards Frank Kabel Frankfurt, Germany Kumar wrote: > Creating more than one pivot table in one page? how do i do that? > > Pls help In step 3 of the pvot table wizard tell it to put it on the same page as the other pvot table. Be careful to leave enough room betwee...

Why does pasted text appear in a table in Publisher 2007?
When I copy text from another program such as my email, it pastes as a tiny text box inside a tiny table. Is there a setting I can choose so that it just pastes in a normal-sized text box without the table? When you copy HTML and paste into Publisher more times than not it will paste with a table. There is a nice little utility you can download, free, that will turn any copied text from the Internet or HTML email into plain text. http://www.stevemiller.net/puretext/ -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "StacyA...

Populate to Multiple fields
I have created a combo box that allows multi-select values. The client would check off the questions that pertain to them, and then I would like the questions they checked off to populate into a subform into indivdual rows. Could someone tell me if this possible? So an example would be: The client checks off question 1, question 5, question 6 etc., then the subform (datasheet view) should look like this: Answer Comments Employee Responding question 1 question 5 question 6 On Fri, 4 Dec 2009 13:32:06 -0800, Dee <Dee@discussions.microsoft.co...

Formula for Dynamic Range?
I am inexperienced and need help: Need to create formula for: A B C D E F Row1 1 2 3 4 5 6 Row2 2 4 6 8 10 12 Row3 Needs to include formula that results in range changes in row2 based on the value cell in row1. I do not have an idea of what to use. Thanks > Row3 > Needs to include formula that results in > range changes in row2 based on the value cell in row1. Can you provide some sample values of what should appear in A3, B3, ... F3 given the values in A1:F1 and in A2:F2, and explain the logic behind how these values are computed? -- Max Singapore http://savefile.com/pro...

setting up multiple email accounts
I'm attempting to add a second email account and at present cannot get beyond test settings. After completing the required entrys I select test settings and it passes all (establish connections and log on) tests except the test email send. I receive a message that indicates I need to check my port (using defaults) and the SSL boxes are not checked. My other account is my personal email address (mindspring) and it is working fine and I'm using defaults. I setup the new email account (a company I'm consulting with) with standard setting except for I have it log onto the...

Conditional Formatting
Hi all, I've tried a couple of the other examples for Conditional Formatting with multiple criterias, that have been discussed on here, but I have been unable to get it to work. I have a spreadsheet with 7 different milestones, which tracks milestone dates for 112 projects. Each milestone has a column of forecast date and a column of an actual date. For example: Milestone 1: Forecast dates in C2 to C113. Actual dates in D2 to D113 Milestone 2: Forecast dates in E2 to E113. Actual dates in F2 to F113 etc. If a milestone has not been reached, the cell is blank. What I'd like t...

WHERE IN (SELECT) with multiple columns
A continuing annoyance is that I can do stuff like this in SQL Server: SELECT * FROM X WHERE A,B IN (SELECT A,B FROM Y) This is getting more complex if any of A or B can be NULL. Is there an alternative in SQLServer 2005+ to implement such predicates? Pete Select * from dbo.Employee e where exists ( select null from dbo.SomeOtherTable sot where sot.StateID = e.StateID and sot.EmployeeID = e.EmployeeID ) You can start there and then experiment. Throw in a " or e.StateID IS NULL "... or something like that. You should post some DDL , some INSERTS and th...

DYNAMIC RANGES #2
I am looking to develop a macro that will allow me to select a range of cells dynamically. I have created a mapping template that maps user defined fields to native fields, but each file that I map has a different number of rows of data. Rather than simply selecting a range of cells statically, I am hoping that there is a macro out there that will allow me to catch all data in a column by dynamically assigning my range to include all rows of data up to the last row containing information and no more. To add to this issue, not all intermediate rows will contain data, but it is important tha...

Dynamic range chart losing its dynamism..??
Help, have set up a named range using offset for the desired chart range, this is a contiguous range with series in rows and the series names in the first column (i can do without the xaxis labels for now) the number of series is high, frm 20 - 150 ish (below the 256 max) i create a stacked are using a nondynamic range that looks as i want then right click on chart, source data, type in data range the sheet name and name range. Thant works, gives me the data i want But when i go back to the chart to review the source datam it loses the formula and just gives me the 'hard-coded' ...

Tables Documentation
Hi, What physical table contains the Vendor Default Accounts? Many thanks, -Itai PM00203 -- Victoria Yudin Microsoft MVP - Great Plains "Itai" <iba@adelphia.net> wrote in message news:%23uSSRe9hFHA.328@tk2msftngp13.phx.gbl... > Hi, > > What physical table contains the Vendor Default Accounts? > > Many thanks, > > -Itai > > > > Yah. that's what I thought, but this table is empty. Yet all of our vendor's default accounts are working well. It must be stored somewhere else. Any idea where? Thanks, -Itai "Victo...

FRx
Hi, is it possible to use FRx to report on tables/views in the Great Plain database that are not part of the Great Plains application data model? I'd like to leverage the existing reporting structure (security, distribution, etc.) to generate reports on other data housed in a separate sql server instance, which would be made available in the Great Plains sql server instance by a view that references a linked server. Is that possible? Thanks, Andreas No, it's not. FRx only reports on GL tables. You might want to check out alternate reporting tools like Crystal and XL Reporter. ...

How to convert the table in word to excel sheet?
How to convert the table in word to excel sheet? Actually the datas in the word is in table format iam in need to convert this datas to excel spread sheet. Hi I would try copy and paste first. In most cases this works (for me) "Santhosh" wrote: > How to convert the table in word to excel sheet? > Actually the datas in the word is in table format iam in need to convert > this datas to excel spread sheet. ...

multiple projects
In VS2005 I have multi-project solution with mostly C# but one VB project to provide a COM wrapper. Two of the projects use an external set of DLLs to create a report, using report definition data taken from an SQL Server. One of the projects creates the report just fine, but the other project errors out when trying to do the same thing. I have checked that the projects are referencing the same versions of the all the DLLs and they appear to be identical. What could the cause of this problem? Rod wrote: > In VS2005 I have multi-project solution with mostly C# bu...

Update table field by content in another field in the same table
My table has OriginatorID and Originator fields. I want the OriginatorID field to update automatically when content is entered in the Originator field. The table that contains the data for these fields is called ASSIGNEES. It has three fields: ID (primary key-autonum), ASSIGN-NAMES (text), and EMAILADDRESS (text). Originator ID in the table is the same as ID in the ASSIGNEES table. ASSIGN-NAMES is the content for Originator field . Thank you in advance for yuor assistance. Linda I am not 100% sure that I understand what you are attempting to achieve however, I will give you...

Excel 2007 CF Multiple criteria
I've always thought CF was one of the more understandable tools used in Excel. Until now. Using Excel 2007. Trying to hilight a cell if it's contents are the same as another cell (Lets say A1 same as B1.). Also want to ignore the CF rule if either cell is blank. It's driving me bonkers. Ideas? TIA. Pierre Select A1:B1 Home tab/conditional formatting / highlight rules /duplicates works a treat best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Pierre" <cowguy@aol.com> wrote in message news:96eb3105-8017-4e7f-817...

id display's funny on form built on qry built on 2 tables ?? (usnint the form Wizard)
i create a query (qryAB) built on 2 tables... tblA and tblB (tblB is a 'child" of tblA) >From tblA the query has 1 fielsds... idA >From tblB the query has 2 fields... idA, idB Now I use the form Wizard to build a Form on qryAB and elect to show all the fields in qryAB on the form The Wizzard ask me "How do you want to view your data?" I click on tblB and the only option is "single form" so I take this option. Now my form doesn't display idA correctly... the 3 fields show like this... tblA_idA -- (note the underbar should show tblA.idA) idB tblB_idA...

Grouping In Pivot Tables #3
Hi I have a pivot table linked direct to an access query. In the pivot table I have grouped the date field by year & month and set the start date as 1 Apr 2005 and end date as 31 Mar 2006. Some of the months have no data so I set the field settings to 'include items with no data' however, this then shows blank fields for Jan, Feb & Mar 2005, if I try filtering or hiding them out it also hides Jan, Feb & Mar 2006. How can I get it to only show from Apr 05 to Mar 06 and include fields where there was no data between those dates? Many Thanks GLS -- GLS Uncheck &quo...

Merging multiple sheets
Greetings all: This may be a simple question, but I have not been able to figure out how to do it. I have a workbook with 5-7 sheets and I want to copy all the individual sheets onto one combined sheet. Is there an easy way to do this? So far my resident Excel expert has only been able to suggest inserting and opening a new sheet, then setting up formulas on the new sheet like ='sheet1'!A1 ='sheet1'!A1 -- Jim Matthews - jcmatthews@fast.net What do you mean by "to copy all the individual sheets onto one combined sheet" ? you have 2 sheets and you want to co...

text wrapping around a table
cant seem to get the text wrapping around a table to work, I go to the table properties of the table I want to wrap text around, then the layout tab, if I choose the object position to be exact, the text wrapping styles seem to make no difference, the result is always a through style, btw this table object the table I am trying to wrap text around is embedded in another table which contains the text to be wrapped around the embedded table, hope that makes sense, clues very much appreciated, TIA -- TIA and take care, Frank Bruhn, Executive Director 508 798-5115, www.SalvageYardWebSolutions.c...