Pivot Table--How can I create from multiple sheets?

Dear Steven:
Thank you very much for your reply and advice.

I am curious now how to create a Pivot Table from multiple sheets.  Whenever 
I try it fails or doesn't allow me to access the Pivot Table/Pivot Chart 
Report menu option.  If you or anyone else has any insight on this, please 
let me know.

Thanks.


-- 
-=- penciline -=-


"steven1001" wrote:

> 
> If you organise your data as follows:
> 
> Date    source    Account   Value
> 1/2/06  Cash      News       2.50
> 1/2/06  Debit     Food      21.50
> 1/2/06  Charge    Clothes   52.50
> 1/2/06  Charge    News      12.50
> 1/2/06  Cash      Food      31.50
> 1/2/06  Charge    Clothes  102.50
> 
> Then a pivot table can easily summarise.
> Example
> In ROW drag labels of Date and Account
> In COLUMN drag label for Source
> In DATA drag label for Value
> 
> The pivot table will create itself.
> 
> Create a second pivot table from the first  (Using Source = from
> another Pivot Table)  if you want an additional view say with Date and
> Source as row headings and Account as Column headings. 
> 
> regards..
> 
> 
> -- 
> steven1001

-- 
-=- penciline -=-
0
penciline (5)
2/21/2006 10:51:27 PM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
622 Views

Similar Articles

[PageSpeed] 0

"penciline" wrote:
> ... how to create a Pivot Table from multiple sheets ..

Perhaps take a look at Debra's page at:
http://www.contextures.com/xlPivot08.html
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---


0
demechanik (4694)
2/21/2006 11:08:00 PM
Dear Max:

I am having difficulty with this setup of choosing multiple consolidation 
ranges.  When I choose a pivot table from a single sheet, it comes out 
correct, with the correct column and row categories that I want to pivot.  
When I choose multiple consolidation ranges, I only get minimal data 
categories.  For example, in my snigle-sheet pivot table I have "category" 
(art supplies, books, clothing, electronics, etc.) as groups that show how 
much I purchased in each group/category.  But in the multiple-sheet pivot 
table, those categories are missing and I cannot pivot them.  I have gone 
about the process of selecting ranges exactly the same way in each type of 
table, but for some reason the multiple-sheet table is missing this important 
data.

Please let me know how I can properly set up my pivot table.

Thanks in advance for your help.
-- 
-=- penciline -=-


"Max" wrote:

> "penciline" wrote:
> > ... how to create a Pivot Table from multiple sheets ..
> 
> Perhaps take a look at Debra's page at:
> http://www.contextures.com/xlPivot08.html
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> 
> 
> 
0
penciline (5)
2/22/2006 5:10:30 AM
The limitations you mention are acknowledged in Debra's page under:
"Limitations of Multiple Consolidation Ranges", with some suggested
"work-arounds", eg: to quote: "If possible, move your data to a single
worksheet ... ".  Hang around awhile, perhaps Debra herself may pop by here
and offer you more insights ..

P/s: I'm eagerly awaiting Debra's new book:
"Excel PivotTables Recipes"
to reach this part of the world <g> ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"penciline" <penciline@discussions.microsoft.com> wrote in message
news:315F2164-FE34-4575-B657-5F034AD607AA@microsoft.com...
> Dear Max:
>
> I am having difficulty with this setup of choosing multiple consolidation
> ranges.  When I choose a pivot table from a single sheet, it comes out
> correct, with the correct column and row categories that I want to pivot.
> When I choose multiple consolidation ranges, I only get minimal data
> categories.  For example, in my snigle-sheet pivot table I have "category"
> (art supplies, books, clothing, electronics, etc.) as groups that show how
> much I purchased in each group/category.  But in the multiple-sheet pivot
> table, those categories are missing and I cannot pivot them.  I have gone
> about the process of selecting ranges exactly the same way in each type of
> table, but for some reason the multiple-sheet table is missing this
important
> data.
>
> Please let me know how I can properly set up my pivot table.
>
> Thanks in advance for your help.
> --
> -=- penciline -=-


0
demechanik (4694)
2/22/2006 6:25:54 AM
Reply:

Similar Artilces:

Can I keep an autoshape tool selected?
For example, if I want to draw multiple lines on a page using the line tool is there a way to just draw, draw, draw instead of going back to get the tool after each "draw". Thanks, Frank wrote: > For example, if I want to draw multiple lines on a page using the > line tool is there a way to just draw, draw, draw instead of going > back to get the tool after each "draw". > Thanks, In Word 2003 or earlier, you can double-click the line, arrow, rectange, or oval tool buttons on the Drawing toolbar (but not the other shapes that are only in the Aut...

OT
Is the Microsoft Exchange website down, by chance? When trying to access the ExBPA Page or the Exchange 2003 SP1 page, or the /exchange/library page, I recieve a Page not found error (Can't post it due to copyright). http://www.microsoft.com/exchange/downloads/2003/ExBPA/default.asp http://www.microsoft.com/exchange/library Error page that launches: http://www.microsoft.com/library/errorpages/smarterror.aspx?404;http://www.microsoft.com/exchange/404.mspx When I try to access the main Exchange page, it now comes up with a 403 Forbidden: http://www.microsoft.com/exchange -- Bob Christ...

Intelligent Pivot Table with Large Data Sets
Pivot Tables are great with small datasets. Does anyone have suggestions of products that can datasource from a ODBC source and manage large datasets (1M+ rows), visualizing the dat in a crosstab/pivottable format ? Unfortunately the underlying logic in the PivotTable/MSquery link isn' too intelligent in that is always wants to read the whole darn databas table before even presenting a field list. I assume this is due to th abstraction of the data created by sticking MS/Query inbetween th Excel pivot table service and the actual datasource? Seems pretty simply from a conceptual POV: 1. ...

create progressive graph for slide show
Hello, I am fairly good at the basic in excel but I can't work out a quick way of doing this: I have a large table of data about 50 by 8000 and am using it to plot graphs. The problem is I want to create a slideshow to show at a seminar which will start off using just the first row of data and then progressively add more rows and display each line graph one at a time. I can't see any simpler way of doing this except to copy and paste each graph into PowerPoint on a time which would take for ever! Ideally I would like the scale to update automatically but that�s not too important. ...

"grouping" a table to prevent change from users
Hi, I would like to group an entire table with a wdContentControlGroup type of Content Control to prevent editing from users except in some wdContentControlRichText type of Content Control. I'm having problem in passing the right Range of the table. If I use: With ActiveDocument Set r = .Tables(1).Range Set objcc = .ContentControls.Add(wdContentControlGroup, r) End With an error "5224" "Wrong selection" arise. If I use: With ActiveDocument .Tables(1).Select Set objcc = .ContentControls.Add(wdCo...

2 pivot tables on one tab
Is it possible to put 2 Pivot tables on the same tab in my workbook? Or does anyone have any ideas on what would be the best way to chart Capital Pircahses? I have 5 coums and 4 rows. The rows are per division, and the columns are for the disposal amount, gain or loss, book value, depreciation & purchase price. I already have one pivot table in my report. Would this be too repeatative? Thanks You can put multiple pivot tables onto a worksheet. If the second one is based on the same data, select and copy the first, select a cell some distance away, and paste it. Each pivot table...

why can't I reopen a document saved in publisher
I am trying to do flyers thru publishers, save the documents for further use but I cannot re-open the document. Can someone help? What happens when you try? -- JoAnn Paules MVP Microsoft [Publisher] "Dsmale5" <Dsmale5@discussions.microsoft.com> wrote in message news:8B70BF36-747C-45C7-B89F-15F58B338E06@microsoft.com... >I am trying to do flyers thru publishers, save the documents for further >use > but I cannot re-open the document. Can someone help? How to troubleshoot a damaged publication in Publisher http://support.microsoft.com/kb/198256/en-us -- ...

Server-Requested Client Action error: MoveCopy unable to create dest msg
I have a user receiving this error message. Server-Requested Client Action error: MoveCopy unable=20 to create dest msg Outlook 2002. Theare are no rules configured.=20 Any Idea? tks, Alisson S. Ara=FAjo ...

Cell extraction from Multiple worksheets
Hi group, I am new to this newsgroup and am hoping that you can help me out. I took a software/hardware inventory of all the computers in my work's network and imported them all into excel, in their own worksheet. So I have one file, with 128 different worksheets, all containing similar information. What I would like to do is create a new sheet, a summary page, where I can pull the value of the same cell from each sheet and list them. I need to have a list of each computer, with computer name, cpu speed, video card, serial number, ect. If the values that I am searching for are in the same...

Find cell numbers in a table so I can multiply
Hello, I have a word document and in the documents header there is a table. This table a has diferent number of cells in each row like: Row one: 2 cells Row two: 4 cells Row three: 2 cells Row four: 10 cells Row five: 2 cells What I wanted to do is multipy 3 cells together thats in row four and show the total in the same row. I have a number in the 3rd cell that needs * by the number in the fith cell that needs * by the number in the seventh cell and totaled in the 9th cell. I know I have to select the ninth cell and select table/formula then what? -- Thanks, Chad I ...

Pivot Table Freezes
I have many pivot tables off a large database and they periodically freeze when I refresh the data. Only solution is to crash out. Any ideas, comments, solutions out there. The machine has been checked out OK. I work on a network - checked out OK. Maybe this is the problem: XL2000: PivotTable Updated Slowly with OLAP Data Source http://support.microsoft.com/default.aspx?scid=237469 Jimbo wrote: > I have many pivot tables off a large database and they periodically freeze > when I refresh the data. Only solution is to crash out. Any ideas, comments, > solutions out there....

Pivot Table Data Ranges
I just upgraded to excel2000, and am finding out that keyboard shortcuts used in 97 do not exist in 2000. Does anyone know of a KEYBOARD shortcut to modify, expand and or create data ranges in the dialog box for entering the range for the pivot. I have many data 'lists' which are added to each month, and when I want to update the corresponding pivot table, I used to simply hit "shift" + "end" + "down arrow" to automatically go to the last populated cell in the list. In 2000, I either have to enter in the cell addy's manually or use the mouse...

Inserting rows into sheet that contains a formula
I have a typical financial sheet where the columns sum (sum(a5:a15)) and the rows sum (sum(a5:h5)). The summation columns are locked and the sheet protected. Is there a technique whereby I can have the user insert a row which will keep the formats and row sum as well as changing the formula in the column sums? Thanks. Rick Hi Rick, The following method is not foolproof but generally it works OK. Insert a blank row between the bottom of data to be summed and the row with the sum formulas. Include the blank row in the sum formula. Protect the blank row along with the formulas and what...

Can Not Send Large Attachment? Maybe? Maybe Not?
When sending large files to Yahoo or other external mailboxes I receive the following error message: #550 5.2.3 RESOLVER.RST.RecipSizeLimit; message too large for this recipient ## My understanding is that this message was rejected by the receiving mailbox - not kicked back from my exchange 2007 server. I'm being told by someone else that is getting kicked by my own exchange server? My understanding is that if it were getting kicked back by my own exchange server because the attachment was too large I would receive: 550 5.3.4 ROUTING.SizeLimit message size exceeds...

COUNTIF across multiple sheets
I have a workbook that has five sheets - September. . . January. I have a range of cells (D6:D17) where I need to count the occurrence of specific text from all sheets . For example: In D6:D17 sheet September has four cells with "fries", two with "malt" and two with "shrimp", the balance of cells in the range are empty. In D6:D17 sheet November has four cells with "malt" and all other range cells are empty On a sheet named Total I want to total the occurrence of "fries", "shrimp" and "malt" on all sheets in the workbook. ...

Sheet you want to copy contains the name...
When I copy a worksheet I get the repeated message box that the destination workbook already contains the name "a", "aa", "prntoutline", "ssd", "q" and "zz". I have to select yes to each message box to allow the worksheet copy to complete itself. I do not have these names within the worksheet (chekced by Insert>Name>Define). How can I delete these nsmaes that do not seem to exist? Thanks. Names can be hidden, so Insert=>Name=>Define may not show them. In the troublesome workbook make a blank sheet active, then go...

Change IP address in Excel Sheet for SQL sever
We have change the IP address of our SQL Sever from 192.168.0.xxx to 192.168.0.4. This results in big problems as all our Excel Sheets with Pivots and SQL Queries in the have stopped working. Cause is that instead of using name we have used IP address in our ODBC set-up so the Excel file has the IP insted of the name of the SQL server. Now, one way to solve this is to save every sheet as XML and then change the adress, then import it and save it as an xls file again - losing graphs etc. Is there anyone that have an alternate solution to this problem and I will be eternaly greatful -...

can't receive calendar invites properly
A company that I do some work for occasionally sends me calendar items. When I receive them at home on outlook 2000, they do not come in as calendar items. They appear more like a text message. Is there some kind of setting I need to change in order to accept their calendar items and have them go onto my calendar at home? They are using outlook 2000 and they run exchange 2003. I'm on outlook 2000 at home. No exchange server. jgross@quonix.net <jgross@quonix.net> wrote: > A company that I do some work for occasionally sends me calendar > items. When I receive them at ho...

URGENT: Creating a chart (Getting the correct input and output) unable to figure out
Hi, I have been trying to create this chart since a long time now and go no clue what am donig wrong. I have some machines in my company and all of them have a end of life for example: Machine 1, at location A with End of Life in 2004 Machine 2, at location B with End of Life in 2008 I want the years on x-axis and the machine locations on y-axis. Afte that I want horizantal lines representing machines which also show th end of life. For a better understanding, see the attached excel file. I have als created an image of the chart I'm looking to draw. Please advise. Thanks. Ker ...

Sizing Data Tables on a chart
When I have a data table attached to a chart and size the chart, the data table does not size proportionately ...

insert multiple page Word document into Excel
How do I insert a multiple page Word document into Excel so that I can view the pages by scrolling down the spreadsheet without opening or editing the Word document? Hi this is IMHO not the way Excel will work (and I doubt this is possible) -- Regards Frank Kabel Frankfurt, Germany "S.W." <S.W.@discussions.microsoft.com> schrieb im Newsbeitrag news:D7087D76-A4F3-46BB-90A9-FEB054B24AEB@microsoft.com... > How do I insert a multiple page Word document into Excel so that I can view > the pages by scrolling down the spreadsheet without opening or editing the > Word doc...

why can't i find the send button and how do I configure e-mail
I seem to be able to write the e-mail but do not have a button to send. the message tells me my address is not configured? Did you create a profile for Outlook to use? http://www.howto-outlook.com/faq/newprofile.htm "LJDICKER" <LJDICKER@discussions.microsoft.com> wrote in message news:211018AE-F489-4002-864E-FAEEC141AF6F@microsoft.com... > I seem to be able to write the e-mail but do not have a button to send. the > message tells me my address is not configured? ...

How to create a Sub validation list in excel?
How can i make a validation list that is dependant on a value in a cell from another validation list, for eg I choose the make of a car from a list then the list next to that changes to the models of that particular make of car, is this possible and if so please could you enlighten me See http://www.contextures.com/xlDataVal02.html -- Regards Ron de Bruin http://www.rondebruin.nl "Simon" <Simon@discussions.microsoft.com> wrote in message news:0BC9D8ED-243C-4376-9C9D-4F1F65AFACBD@microsoft.com... > How can i make a validation list that is dependant on a value in a ce...

Can't open Outlook (Office XP Pro)
When I try to open Outlook (Office XP Pro), I get the following error message: "Cannot start Microsoft Outlook. Errors have been detected in the file C:\Documents and Settings\Administrator\Local Settings\Application Data\Microsoft\Outlook\Outlook.pst." Any ideas on how to fix this? Check out http://support.microsoft.com/default.aspx?scid=kb;en-us;181523 "Bob" <anonymous@discussions.microsoft.com> wrote in message news:01f401c39f84$1e104cb0$a001280a@phx.gbl... > When I try to open Outlook (Office XP Pro), I get the > following error message: "Can...

Excel Pivot tables and charts
How do I retain pivot chart formats that I have made (esp. column colours) after refreshing the data on the source pivot table? Hi, Loss of chart formatting wwith regards to pivot charts is a know problem, which requires VBA to re apply recorded formatting. XL2000: Changing a PivotChart Removes Series Formatting (Q215904) <http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q215904 > Cheers Andy Pauleze wrote: > How do I retain pivot chart formats that I have made (esp. column colours) > after refreshing the data on the source pivot table? -- Andy Pope, Microsoft MVP...