Grouping in Pivot Tables #2

Issue 1:

I have a table which has Job Number on the left, with inner division
by Revenue/Cost Group, then individual Cost or revenue codes. Along
the top are year and month columns.

I have manually selected which cost or revenue codes to include in
"Revenue" group and "Cost" group by highlighting etc and it all looks
OK at the moment. There are about 60-70 cost/revenue codes so this is
a bit of a pain. 

The cost/revenue codes are alphanumeric and are not consecutive in our
chart of accounts. Codes are usually of the format LNNNLLL. Individual
jobs use a subsection of the codes available. 

We are concerned that when we do another data download, any
infrequently used or new codes which don't have a value on any of the
jobs at the moment will appear as separate groups and have to be added
into "Revenue" or "Cost" manually (because they have not previously
been assigned to a group, or didn't appear in the pivot table based on
the previous data download). We're looking for a way of automating
this so it doesn't have to be fiddled about with each month.

Other info:

For the above pivot table, there are just two code groups "Cost" and
"Revenue", but in another table I need to do, cost will be further
split into 3-4 major costs and one group for "other".

Issue 2:

Currently I have the "Revenue" figures for each job coming through as
negative in the data download and the costs as positive. The subtotals
for "Cost" and "Revenue" are being summed to create a calculated
"Margin" field for each job. 

This worked out fine until I constructed a pivot chart and now, of
course, I have the Revenue figures below the X-axis... I had thought
to change the sign of the Revenue amounts in the data sheet before
they pull through into the pivot table, but there doesn't seem to be
any "subtract" calculated field available.... (and for the more
complicated pivot table I would need each of the cost headings to
subtract from the revenue one to get to "Margin".

So I'm not sure if there's a way of "fixing" this so the "Revenue"
subtotals get reversed for the charts only? Or any other cunning way
of achieving what I want in the pivot table so that the chart comes
out right?
 
Would welcome any suggestions of ways that I might tackle these
issues. Thanks.

mousy

-- 
0
12/13/2004 12:00:38 AM
excel 39879 articles. 2 followers. Follow

1 Replies
622 Views

Similar Articles

[PageSpeed] 58

You could add the group to the pivot table source data. Insert a column, 
and use a VLOOKUP formula to return the group for the code. Add the 
Group field as the first field in the Row area of the pivot table.

A calculated item could be created for the Revenue. Multiply the Revenue 
by -1 to return a positive number.

mousetrap wrote:
> Issue 1:
> 
> I have a table which has Job Number on the left, with inner division
> by Revenue/Cost Group, then individual Cost or revenue codes. Along
> the top are year and month columns.
> 
> I have manually selected which cost or revenue codes to include in
> "Revenue" group and "Cost" group by highlighting etc and it all looks
> OK at the moment. There are about 60-70 cost/revenue codes so this is
> a bit of a pain. 
> 
> The cost/revenue codes are alphanumeric and are not consecutive in our
> chart of accounts. Codes are usually of the format LNNNLLL. Individual
> jobs use a subsection of the codes available. 
> 
> We are concerned that when we do another data download, any
> infrequently used or new codes which don't have a value on any of the
> jobs at the moment will appear as separate groups and have to be added
> into "Revenue" or "Cost" manually (because they have not previously
> been assigned to a group, or didn't appear in the pivot table based on
> the previous data download). We're looking for a way of automating
> this so it doesn't have to be fiddled about with each month.
> 
> Other info:
> 
> For the above pivot table, there are just two code groups "Cost" and
> "Revenue", but in another table I need to do, cost will be further
> split into 3-4 major costs and one group for "other".
> 
> Issue 2:
> 
> Currently I have the "Revenue" figures for each job coming through as
> negative in the data download and the costs as positive. The subtotals
> for "Cost" and "Revenue" are being summed to create a calculated
> "Margin" field for each job. 
> 
> This worked out fine until I constructed a pivot chart and now, of
> course, I have the Revenue figures below the X-axis... I had thought
> to change the sign of the Revenue amounts in the data sheet before
> they pull through into the pivot table, but there doesn't seem to be
> any "subtract" calculated field available.... (and for the more
> complicated pivot table I would need each of the cost headings to
> subtract from the revenue one to get to "Margin".
> 
> So I'm not sure if there's a way of "fixing" this so the "Revenue"
> subtotals get reversed for the charts only? Or any other cunning way
> of achieving what I want in the pivot table so that the chart comes
> out right?
>  
> Would welcome any suggestions of ways that I might tackle these
> issues. Thanks.
> 
> mousy
> 


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

0
dsd1 (5911)
12/13/2004 12:15:21 AM
Reply:

Similar Artilces:

Spacing #2
Hi Trim removes spacing in the beginning and end of word. How can i delete a spacing betwee two words (name) ex "De Wit" > i want "dewit" THX Luc You can use: =substitute(a1," ","") to remove all the spaces (leading/trailing/embedded) or =lower(substitute(a1," ","")) if you really wanted lower case. Luc Vandenhoeck wrote: > > Hi > Trim removes spacing in the beginning and end of word. > How can i delete a spacing betwee two words (name) ex "De Wit" > i want > "dewit" > THX Luc -- ...

Viewing Distribution Groups in a different domain
I opened a user account in one domain, using Active Directory, connected to the Global Catalog domain controller of that domain. UIder the Member Of tab, it only lists the distribution groups that the user is a member of in the current domain, and not the lists that are in a different domain. I am using WXP Pro SP2. When using W2K, and I try this, I can view those distribution groups. Any reason as to why I am unable to view them using WinXp? ...

Limitations to # of Decimal places for seconds (time)? #2
Bernie Deitrick Wrote: > Xprezons, > > Are you really recording every 1/10 millionth of a second? And, yes > Excel > limits you to three decimal places on the seconds > > Thanks a ton for your help, Bernie. I guess this is what I have to do. And yes, the system does log time to the accuracy of 6 decimal digits. Thanks and Regards, Xprezons -- Xprezon ----------------------------------------------------------------------- Xprezons's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1505 View this thread: http://www.excelforum.com/showthread....

Excel VBA
Hi again, I have enclosed a timesheet which i want to break down the day activities by a percentage, so if my sheet says an activity took a hour then the day column would tell you percentage of a seven hou day. Also i want the date column date to change each day. Currently it work for one day but then the next day it changes all the above cells to th current date. I have attached the file Cheers Stev Attachment filename: book2.xls Download attachment: http://www.excelforum.com/attachment.php?postid=56396 -- Message posted from http://www.ExcelForum.com ...

Public Folder Auto-reply #2
Hi Is there any way that I can enable an auto-reply for emails that are going to a Public Folder that is mail enabled. The emails will not only becoming from inside th company but externally as well. Thanx for the help Sulaiman yes, you can...on the Administration tab, click Folder Assistant...but be very cautious with this...it's very easy to get into a "mail loop" condition that could conceivably crash the store... -- Susan Conkey [MVP] "Sulaiman" <Sulaiman@discussions.microsoft.com> wrote in message news:9E3D127F-2CBE-40D3-9AC3-333F3D25B465@microsoft....

When form closes it creates a blank record in the table
I have a form that links to a table called 'Suppliers'. Whenever that form closes it automatically adds a blank record to that table. Is there a way to prevent that from happening? Thanks in advance <MichaelJohnson168@hotmail.com> wrote in message news:f63293f9-caa7-4450-bacf-e844c6d5b9c4@e4g2000hsg.googlegroups.com... >I have a form that links to a table called 'Suppliers'. Whenever that > form closes it automatically adds a blank record to that table. Is > there a way to prevent that from happening? If it only happens when you had the form on a new (blank...

outlook mobile access broken #2
I noticed this morning that my OMA was give ASP application error reports when a device tried to connect. I put it down to changing the installed .net framework at the end of last week - using the aspnet_regiis.exe command located in the C: \WINDOWS\Microsoft.NET\Framework directory, I changed from v1.1.4322 to v2.0.50727. So I reverted back to v1.1.4322 hoping it would resolve the issue, no all I get is a 404 page not found - even though there are files there - now I'm a bit stumped. What is going to be the easiest way to fix/repair/recreate this virtual directory? Any pointers would ...

Creating New Mailboxes #2
Hi, ANYONE KNOW WHY AFTER CLICKING CREATE MAILBOX FROM THE EXCHANGE TASKS ON A USER IT TAKES UPWARDS OF 1-2 HOURS BEFORE THE MAILBOX APPEARS? ADVICE/INFO GREATLY APPRECIATED. THANKS PHIL MATHER ZETEX PLC IT SUPPORT Before the mailbox appears where, exactly? In the GAL? In ESM? -- Neil Hobson Exchange MVP For Exchange news, links, and tips, check: http://www.msexchangeblog.com "PHIL MATHER" <PHIL MATHER@discussions.microsoft.com> wrote in message news:577D27BC-B370-4501-A09D-65151B5E901F@microsoft.com... > Hi, > ANYONE KNOW WHY AFTER CLICKING CREATE MAILBOX FRO...

Macro for checkbox (2) #2
Hi Still does not work new message is Sub or function not defined. I normaly just record macros so this is a bid more of a chalange Sub Macro1() ' ' If CheckBoxes(46).Value = True Then Range("O54").FormulaR1C1 = "50000" Range("T54").FormulaR1C1 = "4000" Range("Y54").FormulaR1C1 = "4000" Range("AD54").FormulaR1C1 = "4000" Range("AI54").FormulaR1C1 = "4000" Range("AN54").FormulaR1C1 = "4000" Range("AS54").FormulaR1C1 = "...

PatchFactory 2.0 Released. Professional and easy-to-use patch building tool for Win9x/Me/NT/2000/XP.
Description: Professional and easy-to-use patch building environment that can help you to create instant patch packages for software and file updating. Generated patch packages are small size self-extracting executable update programs in a famous installer style with adjustable user-friendly interface and multilingual support. Enhanced with features like easy-to-use interface including a Wizard mode, powerful patch engine, integrated compression technology, adjustable multilingual user-friendly interface of the update program, this program will most definitely become a valuable asset for softw...

Pivot tables, linking to a named range as a source to a pivot table
I have created a main worksheet within the same workbook of many pivot tables, and I want to use this same worksheet as the source of information to these different pivot table sheets off of which I create charts. I want to use a named range because there are over 4,000 rows in this main worksheet. I am not sure if I need a "=" to start the reference or what to do. I thought I should just use nameofworksheet!database if I name the range "database", but when I point and click to the sheet, it is just inserting the name "database" without the name of the...

beginning balances #2
Hello: This is a new client who is entering invoices and data as of 2006. There are two open fiscal years--2005 and 2006. We created 2005, as the client will need to enter beginning balances for 2006. I just want to make sure that I have the correct steps down. In order to enter beginning balances for 2006, they need to: (1) In GL Transaction Entry, enter a debit or credit to the balance sheet account and a debit or credit to Retained Earnings (or a suspense account) as of 12/31/2005 and (2) They need to close 2005 in the GL, so those beginning balances (BBFs) will roll forward to...

Variant #2
Good Day, I have a VB6 Com object that I am attempting to use in VC++. One of the methods returns a VB6 Variant datatype. When I look at the IDL for the object that value is also defined as a VARIANT*. The problem I have is I am not sure how to deal with this value on the C++ side. What datatype do I use to store this value. Unfortunately, I can really be almost anything. Do I need to store it as a byte array? Any help would be appreciated. Thanks! >I have a VB6 Com object that I am attempting to use in VC++. One of the >methods returns a VB6 Variant datatype. When I look at the...

Sorting table automatically
I have a set of results which are collected into a table B3:AB23. The data is then sorted by the following macro below. I would like the table to be automatically sorted without me having to use the Keyboard Shortcut: Ctrl+y 30-50 times a day. Can this be done? Sub League() ' ' ' Keyboard Shortcut: Ctrl+y ' Range("B3:AB23").Select Selection.Sort Key1:=Range("C4"), Order1:=xlDescending, Key2:=Range("J4") _ , Order2:=xlDescending, Key3:=Range("H4"), Order3:=xlDescending, Header _ :=xlGuess, OrderCustom:=1, Mat...

FRx Forecaster #2
Hello, In a Calc Set when a range of Accounts is used the Calc set does not work (Calc = '1000..5000') Have followed all the TK and it does satisfy all the requirements for a Calc Set to work. Any suggestions would be highly appreciated. Thanks in advance Ajay Ajay-- is the variable on the left side a valid Account? (i.e., does 'Calc' exist in the list of accounts, and in the lineset? Also, check the syntax in the actual calculation. It should read Calc='1000..'5000 with the single quote in front of the variables. "Ajay" wrote: > Hello, &...

Help with combining 2 seperate Worksheet Change Event scripts
Hi, I have 2 seperate Worksheet Change Event scripts that I would like to combine to use in one woeksheet and I'm sure how to do. I am kind of new to VB. The first script is for hiding columns based on a value selected in picklist. Private Sub Worksheet_Change(ByVal Target As Range) 'SalesAid Software If Target.Address <> Range("L2").Address Then Exit Sub 'MsgBox Month(Target) If Target = "All" Then Range("N:BV").EntireColumn.Hidden = False Else lastcol = Cells(6, Columns.Count).End(xlToLe...

How to properly group radio buttons
I have created eight radio buttons. Two of them are in first groupbox and others are in second groupbox. When I try radio buttons, only one of all eight can be checked. How to make buttons in one groupbox not depending on other groupbox ? Thanks ! P. Hi, The first radio button in each group should have the group style set to true. The grouping then works from the first group button with the group style upto and excluding the next control with the groups style. Note the grouping is tab order dependent. Hope this helps Chris Taylor "Peter" <pskvarka@softinengines.com> wro...

WEEKNUM in pivot returns 1900-01-31
Hi! I'm sitting with an excel pivot that has one column (Weeks) which originates from fields formatted like this: =WEEKNUM(G445;2). But in the pivot, the field magically converts into 1900-01-31 (Swedish date)! It looked alright to me until 1 Augusti, when the weeknumber changed from 31 to 32. Then it restarted from 31 to 1 :( I've tried to change the original formula to =TEXT(WEEKNUM(G445;2);0), but the pivot can't sort the list properly, thinking that 21 comes after 1... Any ideas? Thanks alot! /Martin If you just format that field (in the pivottable) as General, what do you...

Report WRITER legends values #2
Hello. I need to modify existing modified report. Where can I find values for legends fields? Thanks. Sofiya. Hello Sofiya, Legend fields are placeholders that may contain pretty much anything, depending of the report you are working with. For example, on many reports, they are used to display the range values used when generating the report. (From-To) One thing you must remember though is that they are always typed as strings so you need to convert them if you want to do any arithmetic operations with them. In conclusion, if you want to evaluate the legend field values for a report,...

Data validation causing problems when using a data form in Excel 2
I have an Excel 2007 workbook that includes data validation set on a number of cells. When using a data form to enter data and I enter an invalid value on the form field corresponding to one of those cells I receive the validation error dialogue that prompts me to retry. I enter the correct data into that field on the form then close the form. My worksheet only has the data relating to the corrected field entered. All other data entered via the form is not entered onto the worksheet. Is this a bug in Excel 2007? I previously was using Excel 2002 and found that when using a ...

Project Green #2
Those of you counting on or worrying about Project Green might want to check out the rants and replies on this forum on the Sept 20 subject entitled ".Net Plans?". ...

lookup table
I have a workbook with two worksheets in it. the first is a list of items a description and a purchase month, the other is a table by month. I an looking to create a method where I can select from a dropdown a product and it will populate the table on the second sheet. Any suggestions? Hi Without getting too complicated, I would suggest using a pivot table, which is designed to summarise data. You can find it under the Data heading. An introduction can be found here: http://www.cpearson.com/excel/pivots.htm Andy. "Brian" <Brian@discussions.microsoft.com> wrote in mes...

Form that changes dafualt value in a table
Hi I made a form that has a text box and a command button, and I want to be able to change the default value in a table, for example the default value in field “price” is $2.00 I want to be able to change it to $3.00 by typing it in my form without have to going to the table design view. So if you got some code for my command button please reply. Thank You! On Dec 13, 8:44 am, Jone <J...@discussions.microsoft.com> wrote: > Hi I made a form that has a text box and a command button, and I want to be > able to change the default value in a table, for example the default value i...

Need Help #2
I have created a custom button for Tag along item.and I am having problem with cursor box. It is not moving down. It is staying where the tag along item rang up. It should move down to empty line, But It is not. How Can I fix his problem. Any one can help me ? ...

How to pick a biggest data from a group of data? #3
I'v tried almost all the formats in Excel,but can't change them. any other idea? some of date are in attachment,please have a try -- yoyo200 ----------------------------------------------------------------------- yoyo2000's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1548 View this thread: http://www.excelforum.com/showthread.php?threadid=27212 I'm not sure I understand the question. If you are seeking the largest number in a row or culumn highlight the data and choose sort from the Data pull down menu. Follow the prompts in the dialog box....