multi sheet pivot tables

Can you create a single pivot table that takes it information from multiple 
worksheets within one excel document?
0
bamf (1)
6/1/2005 10:18:02 AM
excel 39879 articles. 2 followers. Follow

1 Replies
518 Views

Similar Articles

[PageSpeed] 11

You can create a Pivot Table from multiple consolidation ranges, but you 
won't get the same pivot table layout that you'd get from a single 
range. There's an example here:

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

If you can combine the data in your own database, then use that as the 
source, you'll have more flexibility in creating the pivot table.


bamf wrote:
> Can you create a single pivot table that takes it information from multiple 
> worksheets within one excel document?


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

0
dsd1 (5911)
6/1/2005 9:36:38 PM
Reply:

Similar Artilces:

Running Excel Pivot Table with Local cube on http:// Sub-Web???
Hello I have a SQL 2000 local cube that I created on my computer that is accessed by an Excel (XP) pivot table workbook. I would like to place & run both on a secure sub-web on my company http:// website. Both files were FTP'ed to the sub-web successfully. When opening the Excel file I am prompted for the local cube: the prompt is referencing the cube location on my computer where it was originally created with SQL 2000. I am unable to point it to the sub-web location. It appears that the Excel workbook has hard coded the original cube location back to my computer. Is there a way ...

Copy rows to master sheet #2
I have been trying - unsuccessfully - to copy rows that have a value in a certain column to a master sheet. I want a command button to update the master sheet. Upon update, I want the column with the specified value to revert to zero. Any help would be great. Thanks, Carolyn ...

Form
Hi, I am in the process of building an image database. I am almost done with my database. I included a field in a form which is not in my parent table. Now I need to include it in the table. Can somebody explain me how to do it? Thank you verymuch! I assume that the form is bounded aleady to the main table. 1. Open the table in design view and add the field you want 2. Open the form in design view 3. Open the form properties, by clicking on the top left corner of the form 4. Put the cursor in the RecordSource property of the form, on the right you'll see a button with three dots, cli...

simple HTML table generator
Greetings: I need a simple HTML table generator. I'm not proficient in VBA so can't write one from scratch myself. Microsoft provide one for use with excel97, but it no longer functions on newer versions. I need a "bare bones" table generator (i.e. no CSS) that will take a named range and convert it to a HTML table. The only formating other than <TR> and <TD> I want is cell background and font colors, period. Does anyone have such a function, or know where I can get one? Thanks, Scott ...

Pivot Charts
I noticed that you are limited to the amount of editing you can do to a pivot chart such as sizing it differently. Is there a way to allow me to size it, like you would in a regular Excel chart?? Thanks [This followup was posted to microsoft.public.excel.charting with an email copy to David M.. Please use the newsgroup for further discussion.] Not directly, no. PivotCharts are limited in their formatting capability. However, if you create a regular chart from the PivotTable data, you will have more control over the chart. Of course, to do so requires (a) the PT be formatted with...

Converting to an efficient set of tables
I was handed down an Excel file consisting of 47,000 rows and 20+ columns of data. In each column, there is an enormous amount of repetition. The file is over 20 MB and runs slowly. Because of reasons beyond my control at work, I cannot use MS Access to handle this data. So, I would like to create several tables containing the unique values that repeat so frequently and essentially structure the data like I would in Access with various relationships between fields. How can I (at least somewhat) automate this task to replace the cell values with references to another cell on another s...

How to refer to current sheet in a formula
I want to have a named formula which always refers to a cell on the sheet it is on. When I define the formula it is automatically changed to refer to the sheet active when I defined it. E.g. =$B$2*3 becomes =sheet1!$B$2*3. This is no good for use on sheet 2 where I want the formula to refer to that sheet's $B$2 (equivalent of sheet2!$B$2). I have tried entering =!$B$2*3 which works initially but recalculates using the $B$2 on the sheet active when the recalculation is done. -- Poxypig ------------------------------------------------------------------------ Poxypig's Profile: http:/...

Nonrelative Sheet References
Excel is too smart I guess I know when you want to hard code a certain cell it's =$A$1 instead o =A1 but what about when it's Sheet1!A1?? I need the cells to kee looking for their information on Sheet1's cell A1 even when I chang the name of the current Sheet1 and even if there is no Sheet1. Any help would be appreciated - this will help me from having to pas several hundred values everytime I need to print something. Jordan PS. I setup the references already but they follow the sheet when change the sheet name - ex. =Sheet1!A1 becomes =RenamedSheet1!A1 whe I want it to sta...

populating an Access table from a text file
A text file has data in a format like the below: Title1 Cell 1 data Title 2 Cell 2 data Title 3 Cell 3 data .. .. .. Title 10 Cell 10 data Title 11 Cell 11 data .. .. .. Title 15 Cell 15 data .. .. .. Title N Cell N data I guess I can read this using some VBA code posted at http://www.mvps.org/access/modules/mdl0057.htm and http://www.granite.ab.ca/access/readtextfile.htm, add an Instr function to it so that when I read the text file, I know when to insert the data properly in the table. 1. Is there another way I can directly import the data into an Access 2...

Multi selection of an array, ending with sum
*Multi selection within an array, ending with sum*: what combination o functions do I need to use to select a subset within an array and the select within this subset a new selection which I can then sum a colum within this last subset. For example If I have an array of 3 columns and 100 rows and I selec criteria based on 1st column, which produces a new smaller array with columns and 60 rows, I then need to select within this smaller array criteria based on 2nd column, which produces an even smaller subse array from which I sum on the 3rd colum -- sammber --------------------------------...

sending message in MFC multi-threading
I use class CSMTPConnection and CMimeMessage to send email in a multithreading project. when I only spawn one dialog window and send email, there is no problem at all, but whenever I spawn one more dialog window(concurrently), an exception occured. I traced it a little bit. The exception is about a pointer to interface IMultiLanguage while CMimeMessage instance initializing. Codes is as following class CMyDialog :public CDialog { CMyLog * m_log; void fcn(); ...... }; class CMyLog { .... void SendEmail( ....); .... }; void CMyLog::SendEmail(...) { CSMTPConnection EmailConn...

displaying a table on a form so that we can insert a new record at any place rather that at the end
how can i display the table on a form so that the user is able to insert a new record not only in the last, but also in any place of his desire. he must be able to insert a new record even before the first record. how can i accomplish this? can any body help me out please........ hi Ravindar, ravindar thati wrote: > how can i display the table on a form so that the user is able to > insert a new record not only in the last, but also in any place of his > desire. he must be able to insert a new record even before the first > record. Tables in Access (in a database) are a differen...

Strage function in worksheet {=TABLE(I64,)}
I am attempting to understand what appears to be a custom function in a workbook I was given, author unknown. It seems to be some type of index or look up but their is no help on it or macro defined for it. has anyone used or familiar with this? If you need more information I would be happy to provide it. {=TABLE(I64,)} Thanks, Mark On Thu, 26 Feb 2004 19:30:25 -0600, "mkadon" <mkadon@hotmail.com> wrote: >I am attempting to understand what appears to be a custom function in a >workbook I was given, author unknown. It seems to be some type of index or >look up...

Combine cell totals from 5 sheets into summary in Excel
Hi Milor, =SUM(Sheet1:Sheet5!A1) --- Regards, Norman "milor" <milor@discussions.microsoft.com> wrote in message news:440449BE-3F82-40E1-ADFB-DD2C261045EC@microsoft.com... > ...

Automated values in a table
I have a table which inculde two fileds. Field 1 called "Injection" is a Yes/No box. Field 2 called "#of Injections" is a number column. I want to have an automated function, where if "No" is selected in "Injection", "# of Injections" automatically fills in a value of "0". How do i do this in a table? Thankyou! Hi What if you just made the default value of the second field "0" that way you wouldn't have to worry about it if its " No" and if is "Yes" you would have to edit it anyways with a qu...

how do i show text in the data field of a pivot table?
If you put a text field into the data area of a pivot table, it will summarize the field to show a count, instead of showing the text. You can place the text fields in the Row, Column or Page areas, where their text will show. pum75 wrote: -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

Old vb6 / mdb app with "Could not delete from specified tables" er
hi all, I am moving an old vb6 application from a W2K to an XP machine. If I run the app from the XP box and with the .mdb file also on the XP box, I get the above error. If I run the app from the XP box but with the .mdb file on the W2K box, I do not get the error. Any idea where I could start with this one? Thanks for any help! The error is "Could not delete from specified tables" Andy "AndyK" wrote: > hi all, I am moving an old vb6 application from a W2K to an XP machine. > > If I run the app from the XP box and with the .mdb...

tables #2
which tables should be deleted for posted trx in all modules. (financial, ap, ar,inventory, fixed assets, sop, pop). thank you .. ...

Pivot Table Chart Legend
Hello all. I am currently developng a Line chart via a pivot table. One thing I have noticed is that the legend runs in ascending order. E.g. Z at the top down to A's at the bottom. Is there any way of getting the legend "flipped" so it runs A-Z without amending the pivot table itself?? Thank you. Hi, Double check the chart type. A flipped legend would suggest a Stacked Line chart rather than a plain Line chart. Cheers Andy Chiccada wrote: > Hello all. > > I am currently developng a Line chart via a pivot table. One thing I have > noticed is that th...

Structured Table Refs in VBA
I'm confused about the relationship between tables (using structured references) and ranges. I can work on individual cells from a table column, similar to working with a range, using- Dim oSh As Worksheet Set oSh = ActiveSheet Dim r As Range For Each r In oSh.Range("MyRange[ColumnName]") r.Value = 10 Next But if I try to create a range object from a table, using any of- r = oSh.Range("MyRange") r = oSh.Range("MyRange[#Data]") r = oSh.Range("MyRange[ rowvariable, [ColumnName]]") I think I am fai...

Sheet name as cell value
Is there a formula, or "dynamic" method of taking the sheet name an putting it as a value in the same sheet. If the sheet name changes, the cell then updates with the new name. ? -- Jo ----------------------------------------------------------------------- Jon's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=317 View this thread: http://www.excelforum.com/showthread.php?threadid=27051 Hi =MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,36) Arvi Laanemets "Jon" <Jon.1eds5z@excelforum-nospam....

Data, Group got error message Cannot shift object off sheet
I created a command grouping several columns together by going to Data, Group. When I go to click on the "-" sign to group. It gives me an error message;"Cannot shift object off sheet". Why? and how can I fix it. Maybe you have some objects that get rearranged when you hide the columns using your grouping symbols. This may have some suggestions that work for you. It's kind of a similar problem. XL: "Cannot Shift Objects Off Sheet" Error Hiding Columns http://support.microsoft.com/default.aspx?scid=kb;en-ca;211769 Remember to look for comments and merged ...

Property sheets and compstui
Hi, I have property sheet page dialog created using mfc. I want to plug in this mfc property sheets into the cpsui model so I can use this in printer drivers. The only way you can add property sheets in printer drivers is to use the cpsui with the CPSFUNC_ADD_PROPSHEETPAGE. for this I will have to fill in the PROPSHEETPAGE structure and pass to the compsui. Is there anyway I can plug in the user interface created using mfc into the compsui model? Thanks /j If I can fill in pfnDlgProc of the PROPSHEETPAGE structure, I should be able to get this working. How can I get pfnDlgProc me...

Clear all pivot table fields with a Macro
I have a two things I would like to do, ideally with a macro 1. Clear all fields from a pivot table 2. If any of the fields have been filtered - remove the filters I am hoping there is a simple command or two that I have not yet discovered. To put things into perspective: I have multiple pivot tables (all copies of the original table, and each on their own spreadsheet). My plan is to have each table set up for particular queries (that is with different fields and filters). I will then examine the data on each, and sometimes alter the fields as needed. But when I am done I want to press a b...

combining data from several worksheets in a pivot table
I have a workbook with data for sales there is a separate sheet for each month. Every time an agent makes a sale he records it using the date he processed the sale. the sheet then calculates the week number i want to produce a pivot table showing sales by agent for each week however as weeks cross between 2 months i need to be able to include all 12 sheets in the source data for pivot table can this be done. any ideas please Yes - by using "Multiple consolidation ranges" when setting up the table. In my experience this brings more problems than it is worth - because we do not g...