customizing pivot tables

I am trying to create a pivot table based off of data in Sheet2. I need to 
look to be different than any of the autoformats. is there a way that i can 
create my own autoformat, maybe in VBA, that i can use for other tables as 
well?
0
tkaplan (3)
11/15/2005 3:57:05 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
200 Views

Similar Articles

[PageSpeed] 14

Maybe you could record a macro when you format it the way you like.

Then you could run that macro to get that look when you want.

tkaplan wrote:
> 
> I am trying to create a pivot table based off of data in Sheet2. I need to
> look to be different than any of the autoformats. is there a way that i can
> create my own autoformat, maybe in VBA, that i can use for other tables as
> well?

-- 

Dave Peterson
0
petersod (12005)
11/15/2005 5:04:34 PM
ok, here's what i need to do:

I have an excel sheet with the following columns:

YEAR, PERIOD, REGION, CENTER, ME1, ME2, TOTAL, COLLECT

The year can be any year, period is 1 to 13, four regions (east, west, 
south, midwest) and approximately 500 center. me1, me2, total, and collect 
are numeric values.

I need to display the data like this:

Year Header: Year2005
Period Header: Period1
     Region Header: Region1
            Center1 me1 me2 total collect
            Center2 me1 me2 total collect
            Center3 me1 me2 total collect
            Totals: me1 me2 total collect
            Percent: me1 me2 total collect
     Region Header: Region2
            Center1 me1 me2 total collect
            Center2 me1 me2 total collect
            Totals: me1 me2 total collect
            Percent: me1 me2 collect

this would repeat itself for each period based on the amount of centers in 
that region. in example above it would be 3 centers in region1, and 2 in 
region 2.
The totals row needs to total all of the centers in that region.
the percent row needs to take the percentage of total of me1 over total of 
total, me2 over total and collect over total.

so sample data:
2005
Period 1
      West
                 C4 100 150 250  50
                 C9  35  245 280 100
            Totals: 135 395 530 150
            Percent: 25   75        28

So I tried doing this in a pivot table but that does not allow you to put 
the percentage row. I'm hoping there is a macro that i can write to just 
format the actual data into the way i need it displayed with the percent.


"Dave Peterson" wrote:

> Maybe you could record a macro when you format it the way you like.
> 
> Then you could run that macro to get that look when you want.
> 
> tkaplan wrote:
> > 
> > I am trying to create a pivot table based off of data in Sheet2. I need to
> > look to be different than any of the autoformats. is there a way that i can
> > create my own autoformat, maybe in VBA, that i can use for other tables as
> > well?
> 
> -- 
> 
> Dave Peterson
> 
0
tkaplan (3)
11/15/2005 5:54:24 PM
Reply:

Similar Artilces:

Prevent new pivot table items from being selected
I have a pivot table that contains accounts and reports sales by account. Users selected the accounts they want to see and save their spreadsheet. When new accounts are added to the accounts list, users do not want to see the new accounts. They only want to see the accounts they selected from a previous session when they refresh the pivot table. Does anyone know how to prevent the new accounts from appearing in the pivot table? Thanks! Tom, Use another column: Include, with values of Yes or No, and include it in your pivot table, showing only Yes. Then when new data is added, enter ...

Customization: disabling built in features
I would like to disable/remove some of the sections on the left hand side of CRM. For example, on the home screen I would like to remove the Invoices section since we don't use these. And on the Account level I would like to remove the Notes and Contracts sections for the same reason. Is this possible? Keith ...

Multi Table Queries
At my job, I use enter data through generated queries. However it appears that I can only ENTER data in a query when it is based on two tables. If it is three or more, it doesn't let me enter any info. Is this normal? Am I doing something wrong? -Pete Pete wrote: >At my job, I use enter data through generated queries. However it appears >that I can only ENTER data in a query when it is based on two tables. If it >is three or more, it doesn't let me enter any info. > >Is this normal? Am I doing something wrong? Yes, that's common. You should only edit data ...

Unique records in Pivot Tables
Excel 2000 I have data that stores Client Invitation to Tender and Supplier Contract details. Each Client ITT is uniquely identified. However we send out the same ITT to many suppliers so each request for service is given the same identifier, eg Reference Client/Supplier Contract Name Value Status AC/001 Client blah 4,000 pend AC/002 Client blah2 5,000 pend AS/001 Supplier blah3 3,000 pend AS/001 Supplier blah3 3,000 pend When I create my table by Client to get the total value pending ...

Employee Master Table error after V10 SP3
i have updated V10 to SP3, from SP1, and all seems to be fine except if I try to access the employee master table. Either from cards>payroll>Employee, or payroll transaction entry. Anytime I need to do a lookup on an employee id. Here is the message: A Get Change operation on table UPR_MSTR failed accessing SQL Data If I go to the more information or details button: [Microsoft][SQL Native Client][SQLServer] Invalid column name 'EMPLSUFF' [Microsoft][SQL Native Client][SQLServer] Invalid column name 'Dex_Row_TS' -- Doug It looks like an upgrade script failed...

Pivot table novice
As a teacher we tried a new data analysis tool this year in my school which worked really well... except for the fact that it meant manually filling in a table. I'm sure there must be a more efficient electronic way of doing it, but I'm not really an expert on these things. I want to be able to put in a list of data: pupils names with scores from two consecutive tests. Then I want to produce a table with test 1 and test 2 as the headers, and pupils' initials appearing in the relevant box. I have managed to create a Pivot Table to show what I want, except of course, it total...

Pivot Chart: Stop skipping Dates on X-Axis
I have a pivot chart which pulls 2 pieces of information from one table 1 and 1 from Table 2. All three entries are included for each day. Except somedays which may have only the one piece of data from Table 2 or none at all. How do I get the table to automatically fill in all the days whether they have information or not? =?Utf-8?B?UHJpbmNl?= <Prince@discussions.microsoft.com> wrote in news:A5EF53AB-6063-434F-BFC3-43259301751D@microsoft.com: > I have a pivot chart which pulls 2 pieces of information from one > table 1 and 1 from Table 2. All three entries are included for &...

Help to build a table
Please help me to build a table similar to this one The number on the first column will change after 16 times, it will go in sequence from 1001, 1002, etc… The number in the second column will change after 4 times and it will go from 1 to 4 The number in the 3rd column will go from 1 to 4 2nd and 3rd column will keep the same pattern. Cabinet Shelf Location 1001 1 1 1001 1 2 1001 1 3 1001 1 4 1001 2 1 1001 2 2 1001 2 3 1001 2 4 1001 3 1 1001 3 2 1001 3 3 1001 3 4 1001 4 1 1001 4 2 1001 4 3 1001 4 4 1002 1 1 1002 1 2 1002 1 3 1002 1 4 1002 2 1 1002 2 2 1002 2...

Pivot charts formatting
Hi, I want to change the formatting imposed by default in a pivot chart and I want that changed formatting to be retained permanently. For example, for my bar chart, for the different series I have selected colours / patterns but every time I refresh the pivot table / chart; excel falls back to its own colour coding. Any way to get around this? Many thanks in anticipation. Not directly. The only way to simulate this is to record a macro of the formatting changes that you make and then set it up to run whenever the PivotChart calculates. Assuming your pivot chart is on a seperate shee...

Combining Pivot Tables
Hi All, I have a data set of around 100,000 rows which I have imported into excel in two sheets (~50,000 rows each). The data is not in a format that excel can easily parse into a pivot table directly from the source - it requires some formulae in excel to be able to use a pivot table. I have used a pivot table on each of those sheets to summarise the data, and that works fine. However, I would like to be able to get a single summary pivot table from the two sheets (or from the two pivot tables). Is that possible, and if so, how do I go about it? Thanks, Alan. "Alan" <...

Filtering data by custom fields in pivot tables from Visual Report
I have recently upgraded to MS Project 2007 and like the visual reporting feature, except I really need to filter and group the resulting pivot table in Excel by custom fields. For example, for task usage, I cannot filter my pivot table to show only the tasks that have costs > $0, thus my table ends up showing too many lines and a lot of 0's. I would also like to group by department/ function which I put in a custom text field. How do I do this? I saw related posts about saving the file as an Access database and then creating the pivot table by connecting to the datab...

Keeping Custom Properties When Forwarding or Replying to a Message #2
We have emails saved on the file system, that is kept track of by another program. These emails have custom properties attached to them. There is no custom form, just custom properties. These fields were originally added using objPost.UserProperties.Add. We need it so that when someone opens up one of these emails through the file system, and then replies or fowards the message, these custom properties are kept in the reply. This is so the external program can track these emails. Is there a way this can be done? We could have these emails opened up using an Outlook command line that i...

Unhandled database exception: A get/change operation on table 'Bat
During Edit Checks, we get the following message: Unhandled database exception: A get/change operation on table 'Batch_Headers' could not find a record. Then we get this message: This transaction was recovered during normal processing. You may continue processing this transaction. Any suggestions? You might want to run Check Links on payables. Before doing this, make sure you have a backup of your dynamics and company databases. Brenner -- www.KlenzmanConsulting.com "GPI" wrote: > During Edit Checks, we get the following message: > > Unhandled databas...

Grand Substraction instead of Grand Total in pivot table
Hello, I created a pivot table in Excel with Visual Report 2007. I'm using an Outline Code with 2 leaves : Receipts and Expenses. The pivot table created automatically a Grand Total so that $1000 Receipts and $800 Expenses => Grand Total = $1800. My accountant says $200. Generally, it's possible to create calculated fields in the Excel pivot tables. But in the pivot table created by the Visual Reprot, all the Calculated Field options are greyed out. Why ? Thanks for any help I don't know what visual report is, but suspect that it would be more a question for them not Excel a...

How do you define a custom paper size in Excel? (i.e. 11x17)
How do you define a custom paper size in Excel? (i.e. 11x17) Hello- Excel doesn't provide for custom paper sizes, but most any size appropriate for Excel output is already provided _if_ the installed print driver supports it. Go to File>Page SetUp--Page and open the list of sizes. If your installed driver supports 11x17 it will be in the list (although it may be listed as 'Ledger' rather than by dimensions). HTH |:>) "Marcin Rembisz" wrote: > How do you define a custom paper size in Excel? (i.e. 11x17) ...

Customized Task Views Have Disappeared (Take 2)
I was having an issue similar to this original post and followed the instructions given. It worked, but as I have worked with it I have found that it will restore my customized Task views but not my customized Contact views. I can change some of the Send and Receive setting and it will restore the Contact views but not Task views. Somehow I am searching for a way to restore both Task and Contact views. Any ideas? -- Tia, Education and Documentation Specialist Sorry, i posted this in the wrong discussion group. I have posted this correctly and my question has been answered. -- ...

Creating Custom Dialog Boxex (Popup)
Hello, I am trying to create a custom dialog box that will allow me to have the user clarify an entry when a checkbox is checked. I want them to be able to select something from a list that I have created. I have created the popup in Excel but cannot find out how to call the popup into the display when the checkbox is checked. Can anyone help me please? Is this a checkbox on the worksheet? If yes, then if it's a checkbox from the Forms toolbar, assign this macro to the checkbox: Option Explicit Sub testme() Dim myCBX As CheckBox Set myCBX = ActiveSheet.CheckBoxes(Appli...

How do i insert symbol in custom footer
I'm working on Excell 2003 I've been asked to create a custom footer in the left column i have to insert Copyright © 2003. I can't figure out how on earth to insert the symbol if anybody out ther knows could you please let me know Thanks Den one way is to use Insert | Symbol and select the � character and copy it before you go to View | Header and Footer. Then just use Ctrl-V to paste it in the Footer Regards Trevor "Den" <Den@discussions.microsoft.com> wrote in message news:E3AEFB2C-FBF9-4395-95A1-6ECDD7415DAA@microsoft.com... > I'm wor...

blocks of customer database missing
We are running RMS 1.2 and have noticed over the last month a number of customer missing when looked up they mostly seem to to be recent additions from the last few months there are some that are one or 2 missing then there are several with anywhere from 60 -130 in a row missing. there customer records are just gone the customer numbers are sequential and as a block they dissapear. the only other thing we have. noticed is several customers suddonly have duplicate customer numbers when corrected manually it seems a few days later the duplicate is back. We tried to manually recreate this ...

>>>Customer Live Chat Thursday Aug 2nd: Inventory Management <<<
Microsoft Dynamics RMS - Inventory Management Thursday Aug 2nd 10:00am PST Have questions about managing your inventory in Microsoft Dynamics RMS? Want to learn more about item types, tracking inventory, purchasing, or the new matrix item features? Chat with Retail Management System feature experts about these or any other inventory issues. Sign up: http://www.microsoft.com/communities/chats/default.mspx -- This posting is provided "AS IS" with no warranties, and confers no rights. ...

annotated schema and bulk loading into multiple tables...
Hi guys, I'm trying to get XML data loaded into a set of tables using bulk load. The child tables also have an XML column where I want to store portions of the XML. So far the I've got it correctly inserting data into the parent, and able to insert the correct amount of rows in the child tables, but the data in the child tables is empty... The child data is an identity column, a foreign key pointing back to the parent row (empty!), and an XML data column holding the contents of the xml fragment (also empty!). Any help would be greatly appreciated! Thanks. Daniel. Below is ...

Run script from Entity Toolbar Custom Button
I have added a button to the Opportunity Entity toolbar and what I'd like to do is get it to run the change event script on one of the fields on the Opportunity form. I can't seem to get this to work using: JavaScript="crmForm.all.fieldname.FireOnChange();" Presumably this is because crmForm isn't the Opportunity form. Is there any other way I can do this? -- CB ...

how to delete/archive huge data from a table without much issues!
Have a table which sales data from the year 2000. The Database doesn't seem to have any archival mechanism as of now. Now the mgmt feels they don't need data beyond last 'n' years. So delete all those data which is beyond 'n' years is a huge process as i feel it would fill up the transaction log. What would be the best way to approach this problem. Regards Pradeep On 2010-06-18 2:51, SqlBeginner wrote: > Have a table which sales data from the year 2000. The Database doesn't seem > to have any archival mechanism as of now. Now the mgmt feels...

PIVOT TABLE ALL option
Is there any way to remove the "All" option that comes in the page fields in a pivot table?? Nope... All is there to stay... -- HTH... Jim Thomlinson "Aman" wrote: > Is there any way to remove the "All" option that comes in the page > fields in a pivot table?? > As Jim said, you can't remove the "All" option in the page field. With programming, you could select another item if the user selects "All". For example, the following code is stored on the worksheet's code module: Right-click the sheet tab, and choose Vi...

How to add custom toolbar(with edit box and Spin controlls) in my application
hi frnds i have to add a toolbar in my application and toolbar should have two edit box with spin controls and 4 buttons and i have to add it in my existing application. so plz help me, how to do it plz reply with code n suggestion ur frnd Premanshu sInha See here: http://www.codeguru.com/cpp/controls/toolbar/placingcontrolsintoolbars/article.php/c2547/ --- Ajay ...