Pivot using three sheets

I use Pivot table (Excel 2000) regulary for summarising my 
data.
Now my data is spread over three sheets. How can I include 
the data from all the three sheets in one pivot table. 
Because as I understand, the Pivot table can include only 
values only from one sheet. Please guide me.
0
8/1/2003 8:18:00 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
553 Views

Similar Articles

[PageSpeed] 55

On the very first step towards creating a pivot table, instead of just hitting next, take a look
at the multiple consolidation ranges option.

--
Regards
           Ken.......................    Microsoft MVP - Excel
                   Sys Spec - Win XP Pro /  XL2K & XLXP

----------------------------------------------------------------------------
  Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



"Neeraj" <neeraj_chow@yahoo.com> wrote in message news:07c501c35805$6bb439c0$a501280a@phx.gbl...
> I use Pivot table (Excel 2000) regulary for summarising my
> data.
> Now my data is spread over three sheets. How can I include
> the data from all the three sheets in one pivot table.
> Because as I understand, the Pivot table can include only
> values only from one sheet. Please guide me.


0
ken.wright (2489)
8/1/2003 11:25:21 AM
As others have suggested, you can create a PivotTable from multiple 
consolidation ranges. However, you won't get the same pivot table layout 
that you would from a single range.

If customer is the first column in your data source, the row heading 
should show the customer names. If remaining columns are Units Sold, 
Product#, Unit Price and Total, the column area will show each of those 
headings. You can change the function that's being used by the data 
value, but it will use the same function on all these columns.

The Pivot Table would contain some meaningless data, such as sum of 
Product# or columns full of zeros for database columns that contain 
text. To avoid this, you can rearrange your database columns, and then 
use data ranges that only include the columns that you want to total.

If possible, move your data to a single worksheet, and you'll have much 
more flexibility in creating the pivot table.


Neeraj wrote:
> I use Pivot table (Excel 2000) regulary for summarising my 
> data.
> Now my data is spread over three sheets. How can I include 
> the data from all the three sheets in one pivot table. 
> Because as I understand, the Pivot table can include only 
> values only from one sheet. Please guide me.


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

0
dsd (439)
8/1/2003 4:35:34 PM
Reply:

Similar Artilces:

sending mail using an alias email address
We are using Exchange Server 2007 with Outlook 2007 clients. I have several email addresses listed under my mail account on the Exchange Server for which I "receive" email. However, the server will not allow me to "send" email using any of these email addresses - as it only allows me to send using the primary address for my email account. I get a message saying "You are not allowed to send this message ... on behalf of another sender without permission to do so." What settings do I need to change on the Exchange Server so that it allows Outlook 2007 ...

Uncheck "always use the selected program to open this kind of file" by default
http://www.vistax64.com/newreply.php?do=newreply&p=1136177 Andrew;1136177 Wrote: > If I right click on a file there is usually the option to"Open with..". > Selecting this option list possible programs with which to open the > application and, below this, there is a check box labelled "always use > the > selected program to open this kind of file". The check box is always > checked, so clicking on OK permanently associates the selected program > with > the file type in question. > > However, I usually use this option to op...

using parameters
I have a form which the user selects the BlockNo. The other information that is entered in the form is : 1) NoOfRecordedTrees - RT 2)NoOfSurroundingTrees - ST When the BlockNo is entered, a query runs which picks up fertiliser rates for this Block for all sectors within that block. With RT and ST - it should do a calculation such that it uses Rate/ sector * (RT+ST) to find how much fertiliser is needed for each sector in each block. I would like to add a column to the existing query showing FertiliserAmt using these parameters. How do I specify them within the query. Thanks for your great...

how can i set up my pop and smtp account using proxy server in ou.
"Abhishek" <Abhishek@discussions.microsoft.com> wrote in message news:90EE645B-97D9-4145-A368-E26743C547F9@microsoft.com... You'll need instructions from your ISP. -- Aloha, -Ben- Ben M. Schorr, OneNote-MVP Stockholm Consulting Group/KSG http://www.scgab.com Microsoft OneNote FAQ: http://home.hawaii.rr.com/schorr/computers/onenotefaq.htm **I apologize but I am unable to respond to direct requests for assistance. Please post questions and replies here in the newsgroup. Mahalo! ...

Finding all queries which use a table
Hi, Does anyone know of a tool that can scan all queries in a database and find if a certain table is used? I have a table called tblCustomerRollup which is old and outdated. I want to see which of the 500 queries in my database use this table without opeing every single one of them? Thanks, -- Chuck W Chuck Sounds like a variation on Search/Replace. Try searching online for "Database Documenter" as a starting point. A couple of the commercial tools I've used include FMS, Inc.'s Total Access Analyzer and Black Moshannon's Speed Ferret. There are a lot of fr...

Re: Using alias address
Brian Tillman wrote: > Vince <vinresp*@swhome.com> wrote: > > >>We use an exchange server at work for e-mail. I have set up an alias >>that I use for receiving mail but I wanted to use the alias for >>sending all the mail as well. I was told that it cannot be done in >>exchange but I can use a POP server. > > > I don't believe that's always true. For example, I have several accounts in > Outlook where I work, all within the same profile. Only one of those > accounts, the Exchange account, uses my work address. The ot...

Can Outlook 2003 use MSN Messenger INSTEAD of Windows Messenger?
I didn't get an answer to this question last week so I'm re-posting... I recently purchased a new computer (Windows XP Home Edition w/SP2) and loaded up all of the available updates to the OS, Internet Explorer, etc. Next, I installed Office 2003 Professional. I disabled Messenger integration with Outlook 2003 as discussed in other posts here. Next I installed MSN Messenger 6.2 and it seemed to run properly as a stand-alone application. So I re-enabled Messenger integration on Outlook 2003. The next time I booted up and ran Outlook, the Messenger icon appeared in the taskbar...

Right click in Pivot Table or on Entire Column
I have added items to the right-click menu that popups up when you have a cell or cells selected. But when you are in a Pivot Table or have an entire column selected the right-click popup is different. Is there a way to add an item to the right-click popup menu when you are in a Pivot Table or have an entire column selected? Thank you for your help. Steven Never mind. This one was right in the help section. I should have looked first. Thank you, Steven "Steven" wrote: > I have added items to the right-click menu that popups up when you have a ...

Creating Contacts For Accounts Using...
Hopefully as Microsoft CRM matures, many small time saving features will be added. One that should be a priority is the ability to add a new contact for an existing account using the common account information, i.e. address, phone number, web address, etc. Retyping the same company address in each time is not very productive. Thank you, Ed Podowski ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, f...

Scroll horizontaly with mouse, create same system used to scroll .
Hi, I think it would be great if mouses adopted a second scroll button, for horizontal scrolling, just like the vertical one .... Indeed, when you work with wide Excel spreadsheets, you can easily scroll down but to scroll from left to right or vice-versa, you have to use the scroll bar or arrows and it's annoying... So, am I a millionnaire yet??? Hi Frederic, > I think it would be great if mouses adopted a second scroll button, for > horizontal scrolling, just like the vertical one .... Indeed, when you work > with wide Excel spreadsheets, you can easily scroll down b...

outlook in sub-domain to set use root-domain question!!!
Dear Sir Please see below more details,(We are using special railway line between Head office in Taipei and branch office in Tao-Yuan) Head office in Taipei: aaa.com(Root domain) Dc server * 2(One of it is GC Server), Front-End Exchange 2003 *1, Back-End Exchange 2003 * 2(One is named mail1, another is named mail2 ) Branch office in Tao-Yuan: bbb.aaa.com(sub-domain) Dc Server *1(No GC Server,No Exchange Server) After using ADMT v3 Tool, when I transfer an account from root named aaa.com(ou) to bbb.aaa.com. After I ins...

Pivot tables and Macros
I was looking to be able to manipulate (ie change selections from the drop downs, not change the fields in the table or anything)and print from a pivot table using a macro. So how do I go about doing that? When I set up a macro by recording the actions that I want to do, I always get an error message when I try to run it as a macro. What specific steps are you recording, and what error message do you get? Can you click the Debug button, and see the line of code that is causing the problem? Dust For Eyes wrote: > I was looking to be able to manipulate (ie change selections from the >...

Outlook first use
Hi, when I open Outlook in a workstation for the first time, Outlook open setup and ask user to create a new profile. I have a utility to create profile automaticaly and this setup is deleting existing profile. Is it possible to Outlook don�t ask to create a new profile for the first time? Tks. Alex ...

Adding a button with a function on protected sheets.
How do i add a button to 'reset/clear' the data on a worksheet that is protected and uses VLookup data (from another worksheet). Everyday this table will have data chosen from combo boxes or manually entered in allowable editable fields and at the end of the day after the files is saved, I need to clear that data for use on the next day. How is this Reset button applied with allowable edit ranges, VLookup data and a protected worksheet? Thanks There's several ways to do this. 1. Instead of straight vlookups, use =IF(ISBLANK(VLOOKUP(....)),"",VLOOKUP(....)) wher...

Pivot Table in Excel
hi, I have a problem using the pivot Table in excel 2000. Earlier the location of the pivot table in excel was pointing to say c:\sales.mdb. Now the location has changed to D:\Sales.mdb. someone please tell me the place to change in the excel to reflect the same. So that upon refreshing the document i can see the latest data. Currently i am getting a error message when i try to refresh, but its not prompting to change to alternative location. If i edit the excel file in a notepad...i can see the location pointing to c:\sales.mdb. Thanks in advance for any help regarding this..expect...

Calculating Correlation using arrays
I have a sheet full of data for many products in chronological order. Column A is Date of manufacture Column B is time of manufacture Column C is the product Column D is a measurement from the automatic control system Column E contains occasional manual measurements for calibratio checks Up till now I have split the data out by grade and checked calibratio using pivot tables and also checked slope and offsets. After gainin exposure to all kinds of clever functions via this board I now wonde whether it would not be possible to automate these checks in some way ie on a separate sheet I list t...

can I use 11 x 14 paper in office documents?
Is it possible to use a larger sheet of paper when using publisher? Yes. First select the paper size in the Printer Setup. -- Don Vancouver, USA "Prairie Inn" <Prairie Inn@discussions.microsoft.com> wrote in message news:FC032907-D965-4DE9-891D-40DF9C6E9C8C@microsoft.com... > Is it possible to use a larger sheet of paper when using publisher? If your printer can handle it, yes. -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 "Prairie Inn" <Prairie Inn@discussions.microsoft.com> w...

Online Restore using NT Backup has no edb.chk or edb.log files
I have a single site with four servers running Exchange 5.5 SP4 on NT4 SP6a. I am using an internal 35/70 Compaq DLT. When I back up two servers at the same time using online method, I am missing the edb.log and and edb.chk files when trying to restore the db's. Is there a known issue for this? Thanks, Jim When you make online backup, you are backing up the database content perse, the logs files will be skipped because ntbackup cannot back up open files. I recomend you to adquire a third party backup software with open files and exchange database options, like Veritas to ensu...

use excel to create PP slides?
I'm not sure if this is possible but I have a list of information in an excel table and I'd like to auto matically generate a slide for each row of data. for instance, I have a list of products and each product is on it's own row with 6 product features in the following columns. How can I take that info from excel and create a features slide for each product using the information from the following columns. I don't mind cutting and pasting the list - it doesn't have to be fully automatic but as I have over 1000 products I desperately don't want to reype ...

Moving certain data to different sheet
I need to move data that meets a certain criteria, to another sheet within a workbook. For instance, if a column of data is for a certain ZIP code area, I need it to automatically copy to a sheet for that city. Say, 40202 would go to the Louisville, KY sheet. Because Louisville has multiple ZIPs, I would need only the data that begins with 402 to go to that sheet. Lexington KY's data, which begins with ZIP code 405, would go to its own sheet. Macro? Formula? Thanks! This can definitely not be created with a formula. I suggest that you make use of the macros. Rgrds, Kris...

Welcome Sheet?
Hello, I need a sheet to be in a workbook and it will be a sort of welcome screen, what I want this sheet to do is be visible on open but NOT a tab on the bottom(where it says sheet 1, sheet 2, etc...). Is this possible, if so ... How? Thanks -- Stu Stu, Here is one way, using something like this, put this in your worksheet code Private Sub Worksheet_Deactivate() Sheet1.Visible = xlSheetVeryHidden End Sub And this in the this workbook code Private Sub Workbook_Open() Sheet1.Visible = xlSheetVisible Sheet1.Select End Sub You could also use a userform as a welcome screen -- Paul B Alw...

create a letter with the data fron work sheet
i have a data sheet for the order.like people order staff lik tea,coffee...etc,i ve data including date,name,department,.....etc. when i put the data i want a letter format which picks up the data fro the sheet then i can send it to the person who made the order as confirmatino letter. like it the letter starts dear "......"(it could pick the name fro c1) also how can i create this letter? does it have to be in excel ?? help pleaseeeeeeeeeeeeeeeeeeee!!!!!!!!!!:( :( :( : -- Message posted from http://www.ExcelForum.com As answered in microsoft.public.excel and microsoft.public....

Using BGInfo Command Line Parameters
I've read the Help file in BGInfo - but can't figure out how to use Command Line Parameters. When I click on my startup icon, I would like to invoke certain parameters. The line I came up with is: "C:\Program Files\BGInfo\BgInfo\Bginfo.exe /timer:99 /popup /taskbar /rtf:C:\DATA\BGINFO /nolicprompt" Obviously, this is wrong. My Target line reads: "C:\Program Files\BGInfo\BgInfo\Bginfo.exe" My Start in line reads: "C:\Program Files\BGInfo\BgInfo" Please help me by telling me what, exactly, should go in each line. I would also like t...

Creating and pasting using VB
I need to create an email and paste a current selection in microsoft excel using visual basic. I have just started to work on this so I don't know what all I have to include. If I need mapi add-ins or anything else added please let me know. I already have the sub routines set to copy the data that I need out of excel but now I need to email this data automattically. Any help would be very helpful. Thanks, Matt ...

Use Your Brain
All, Here is an example of what extreme boredom can do to a person. Excel 2002 / Window XP :D Have fun. Attachment filename: use your brain.zip Download attachment: http://www.excelforum.com/attachment.php?postid=386422 --- Message posted from http://www.ExcelForum.com/ Ken, Very cute. You must have a lot of free time on your hands. John "Pyball" <Pyball.ynt5z@excelforum-nospam.com> wrote in message news:Pyball.ynt5z@excelforum-nospam.com... > All, > > Here is an example of what extreme boredom can do to a person. > > Excel 2002 ...