Adding alternate columns

I work on a schedule where I record ticket count and ticket sales for
each show, listed chronologically. 

Column A = week ending date 

Column B = # Tickets sold for April 5 show 
Column C = Ticket Revenue for April 5 show 

Column D = # Tickets sold for April 9 show 
Column E = Ticket Revenue for April 9 show 

Column F = # Tickets sold for April 12 show 
Column G = Ticket Revenue for April 12 show 

Column H = Total # Tickets sold for all shows 
Column I = Total Ticket Revenue for all shows 

In the Totals columns, the Total Tickets formula is "+B4+D4+F4", and
the Total Revenue formula is "+C4+E4+G4" and so on for each row.... 

When I get a new show that occurs chronologically in between two
existing shows, I insert two columns in the appropriate place and then
input my new data. Every time I do this, however, I have to adjust my
Totals formulas to include those new columns (and so each time a show
is added, the Totals formulas get longer). 

Is there a formula I can substitute in my Totals columns that will
automatically include new columns?


-- 
LACA
------------------------------------------------------------------------
LACA's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30381
View this thread: http://www.excelforum.com/showthread.php?threadid=501288

0
1/14/2006 1:28:17 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
1478 Views

Similar Articles

[PageSpeed] 10

Not sure what your column headers are but let's assume that one is "Tickets" 
and the other "Revenue" then the formula would be:

=SUMPRODUCT(--($A$1:G$1="tickets"),$A2:G2)
=SUMPRODUCT(--($A$1:G$1="revenue",$A2:G2)
leave the reference to column A (date column) in the formula, that way you 
can insert columns anywhere between column A and your totals columns without 
messing up the results.

HTH
JG

"LACA" wrote:

> 
> I work on a schedule where I record ticket count and ticket sales for
> each show, listed chronologically. 
> 
> Column A = week ending date 
> 
> Column B = # Tickets sold for April 5 show 
> Column C = Ticket Revenue for April 5 show 
> 
> Column D = # Tickets sold for April 9 show 
> Column E = Ticket Revenue for April 9 show 
> 
> Column F = # Tickets sold for April 12 show 
> Column G = Ticket Revenue for April 12 show 
> 
> Column H = Total # Tickets sold for all shows 
> Column I = Total Ticket Revenue for all shows 
> 
> In the Totals columns, the Total Tickets formula is "+B4+D4+F4", and
> the Total Revenue formula is "+C4+E4+G4" and so on for each row.... 
> 
> When I get a new show that occurs chronologically in between two
> existing shows, I insert two columns in the appropriate place and then
> input my new data. Every time I do this, however, I have to adjust my
> Totals formulas to include those new columns (and so each time a show
> is added, the Totals formulas get longer). 
> 
> Is there a formula I can substitute in my Totals columns that will
> automatically include new columns?
> 
> 
> -- 
> LACA
> ------------------------------------------------------------------------
> LACA's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30381
> View this thread: http://www.excelforum.com/showthread.php?threadid=501288
> 
> 
0
pinmaster (74)
1/14/2006 2:07:01 AM
Brilliant.

Thank you!


-- 
LACA
------------------------------------------------------------------------
LACA's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30381
View this thread: http://www.excelforum.com/showthread.php?threadid=501288

0
1/14/2006 4:39:31 AM
JG

Would you mind explaining the role/purpose of the "--" in your formula?


-- 
LACA
------------------------------------------------------------------------
LACA's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30381
View this thread: http://www.excelforum.com/showthread.php?threadid=501288

0
1/14/2006 4:45:19 AM
Reply:

Similar Artilces:

adding field to form
Hello, I have made continiuse form with 10 field that works (text box in detail and lables in form header) but when i want to add one more field that text box is in detail part of form and lable in form header part ,I can not save my form.and access goes to repair mode after saving and there is no change when I reopen my form. thanks. ...

Off topic: VB6 question
sorry this is way off topic. using _vb6_: i'm trying to add/replace nodes to an xml document via CreateTextNode: xml = "<item>floogle</item>" set newNode = oDom.createTextNode(xml) call dDom.replaceChild( newNode, bloogleNode) It works ok. But I can no longer find the new node in the document via an xpath query. When I save the doc to disk I can see the new node but its been escaped, presumably because it was a text node: <root> <item>wayheyhepahole</item>&lt;item&gt;floogle&lt;/item&gt; </root> I guess my question is: H...

Auto-Forward and Alternate Recipient
I have an account on an exchange 5.5 server which has been configured to forward mail to a different account on an exchange 2003 server using the alternate recipient option. Typically if the exchange 5.5 account received an e-mail, the forwarding works. However, if the exchange 5.5 account receives an e-mail that was auto-forwarded to it via a rule, the e-mail will not be forwarded to the exchange 2003 account. Any ideas what the problem is? I believe that Exchange is trying to protect itself from a mail loop. I've never heard of this particular issue before, but I'd assume...

Copying matched criteria plus relevant columns to new worksheet
I want to use code in a macro to search for criteria in a worksheet which allows the matched data to be copied (along with other relevant columns) to another worksheet in the same file. Would this involve some sort of loop? Any help apreciated. Thanks in advance, Matt there are several ways to do it. if you want to copy a hundred cells for three columns every time something in row 3 is matched somehting like the following could be used. sub try() tar=1 ' target column in sheet 2 for c = 1 to 34 step 3 ' this will be the look up column in sheet 1 if sheets("Sheet1&quo...

Why is it picking multiple columns
When I click on a cell, it selects 8 cells. How do I reset it to only pick a column to work with? Thanks in advance. Bob Maybe you have merged cells. Select that group of 8 cells Rightclick|Format Cells|Alignment tab|Uncheck Merge Cells The Dog Breeders List wrote: > > When I click on a cell, it selects 8 cells. How do I reset it to only pick a > column to work with? > Thanks in advance. > Bob -- Dave Peterson try the f8 key or beat up your scroll mouse -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "The Dog Breeders List" &...

adding new columns sheet with external data
I have a excel sheet that I import data from a external database and then i added columns to the sheet that have entries made by me. When the data refreshes if the data is no longer coming from the external it leaves the data that i entered still there. I want it to delete the whole row and leave no data at all external or internal for that line that now does not exist can this be done? ...

Upgrade AD when adding 2008 Server to Domain
We have added a new Windows 2008 Server (which will be a Terminal Server) to our Windows 2003 AD Domain. Do we need to update the AD Schema? Our AD Policy acts weird on the new server. Sometimes printers do not show up, then the run command is enabled, then at times the task manager is enabled. We try and lock down all of our TS Servers. Thank you for your assistance. Howdie! Patriot schrieb: > We have added a new Windows 2008 Server (which will be a Terminal Server) to > our Windows 2003 AD Domain. Do we need to update the AD Schema? Our AD Policy > acts weird on...

Conditional formatting alternative
I was able to highlight some cells using conditional formationg. Unfrotunately the cells are not next to ehac other (there are blank cells in the middle). Since I don't need those blank cells I can just delete them or sort the column so all the cells with information move to the top, the problem is that when I finish sorting and the cells move up I lose the format I applied on conditional formatting. is there any other way to apply format (change color) to a cell depending on 2 other cells? This is what I have: Highlight in bold row 2 if A2 -a date- is between A1 -date #1- and A2 -d...

AD connector and directory replication question
We're running Exchange 5.5 and have an external site running Exchange 2000 pushing and pulling GAL information to our site using their AD connector. We want to be able to send Outlook meeting invitations to each other and understand that in order to do this must allow the messages to be sent in Rich Text. My question is that is there a way to have the external site push their address list to us, and have the "Allow Rich Text in messages" option checked by default? (seems that when we receive their address list the users are imported as custom recipients without this item checke...

Average time from date time column?
I have a column that is in general date format, 6/19/2007 5:34:23 PM. In another column I have a value between 10 and around 50. What I have to do is get the average of this number by time in a day. So if the number is 10 all day, then the average is 10. If the number is changed during the day, say its 5 for half the day then 10 for the other half of the day, then the number I'm looking for is 7.5. If the number is 5 for 1/4 of the day and 10 for the other 3/4 of the day then the number would be around 8.5..... if you follow what I'm trying to say. So far I have been a...

Adding plain text to a database?
I have some instructions (about 2-3 paragraphs) that need to accompany one of my databases. Rather than sending them separately where they might get lost, how could I include them within the database file? Why not simply create a form and enter the text there. then you can add a button so the user can access the info when they need it. -- Hope this helps, Daniel Pineault "Cheese" wrote: > I have some instructions (about 2-3 paragraphs) that need to accompany one of > my databases. > > Rather than sending them separately where they might get lost, how co...

Adding a number to a spreadsheet
I am trying to create raffle tickets in Excel, and I need to have a ticket number on the bottom of each ticket (starting with 001). Is there any way I can make the program automatically add the numbers, or will I have to do it manually? There will be five, two-inch high tickets on each page, and each ticket will have two halves. The number will have to appear on each half of the tickets. Allyson You have given little info as to how you want to achieve this and my method may want you to go out and buy them!! 1) Type a 1 in A1 and a 2 in A2. Highlight both cells 2) Grab the 'fill handl...

adding lines to added data
I have added data to an existing worksheet but when printed the row lines and column border lines do not show. How do I add the lines to the added border to match the existing lines? ...

How to Select large amount of data in a colum that equals column from another table.
Ive got a huge table and I want to select the data in one column that is equal to that of a column in another table? is there an easy way to do this? Thanks One way, if you know the parameters of the original, is to type the references in the name box and hit <Enter>. For example, click in the name box and enter: E1:E65000 Then <Enter>. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Jennifer Burnel" <jenburnel@babooshka.net> wro...

Offline Folders: Added Sub-folders are not made off-line
I am using XP and use offline folders to keep important data on / up to date on my laptop. I have noticed that if I have a parent folder set to available offline, if I add a sub-folder then this sub-folder and its contents are available offline. However if someone else adds a sub-folder then this sub-folder (and its contents) are not automatically available offline (even though the parent folder is and theoretically all subfolders beneath it). Has anyone else noticed this problem? Is there a fix? As it is I have to periodically look through the folders to see if any new ...

How do I average a column without including zeros
I want to average a column that is all percentages, but I want to excluding any cells with a 0%, what formula would I use? Thanks!!! Maybe.. =SUM(A1:A10)/COUNTIF(A1:A10,"<>"&0) KMHarpe wrote: > > I want to average a column that is all percentages, but I want to excluding > any cells with a 0%, what formula would I use? Thanks!!! -- Dave Peterson There may be a snappy built-in function in Excel to do this, but until someone responds with it, this formula will work: =SUM(D1:D5)/COUNTIF(D1:D5,"<>0") .... where D1:D5 is the range containin...

Multiple columns in stack graph
Hi, I have four pieces of data which I wish to stack in two columns, I can only manage to create a graph with one colum containing all four pieces of data. For example: North Rep1 - jan05 - feb05 - mar05 - etc Rep2 - jan05 - feb05 - mar05 - etc South Rep3 - jan05 - feb05 - mar05 - etc Rep4 - jan05 - feb05 - mar05 - etc By month, I would like to see two columns, one containing north and one containing south. (North is a stack of reps 1 & 2, south of reps 3 & 4). I hope this makes sense, and someone can help me. Thank you Amanda Set up your data such that there are three ro...

AD Backup
What is the difference between Critical Backup & System State backup in Windows Server 2008 and which one is recommended and what are the restoration differences using these two types of backups? This has nothing to do with Active Directory and would encourage you to repost in the General Windows forum. -- Paul Bergson MVP - Directory Services MCITP - Enterprise Administrator MCTS, MCT, MCSE, MCSA, MCP, Security +, BS CSci 2008, Vista, 2003, 2000 (Early Achiever), NT4 Microsoft's Thrive IT Pro of the Month - June 2009 http://www.pbbergs.com Twitter - @pbber...

Alternate axis at top of chart
i have a number of X-Y scatter plots where I need to have either only an alternate abscissa axis at top of chart or alternate ordinate on right and abscissa on top. I can easily do the alternate ordinate but can't find a way to do the alternate abscissa. How do I do it. the only option I see at moment is to redraw graphs in Visio and then add the axises. I prefer to use Excel XP but can use 07 if need be. Thanks in advance for any suggestions! What you need are secondary axes, and you need multiple series in order to get secondary axes. Right click one of the series, choose Forma...

Dynamic range (column); include only rows before blank row above more data
Hello: One worksheet will contain more than one table holding lookup values (prices, CPU speed, etc.). They will be located one on top of another, with possibly some other data between tables, separated by at least one blank row. They will be expandable by row, as new prices, CPUs, etc. are added. I want the range name to expand and reflect all the rows in a table. Using a column as an example of tables, a (in A1) b c d e f represent 2 tables (ranges). Excel Hacks has the code for Define Name "Refers to" as =OFFSET(Sheet1!$A$1,0,0,MATCH("*",Sheet1!$A$1:$A$100,-1...

Addition of two Column with Condition
Hi I need a formula that calculate two or more column with given condition, I used SUMIF and DSUM , but it cant worked. On Feb 21, 4:12=A0am, Tayyab <sheikhtay...@gmail.com> wrote: > I need a formula that calculate two or more column with given > condition, I used SUMIF and DSUM , but it cant worked. Difficult to answer without a specific example. Perhaps one of the following will help: =3DSUMIF(A1:A100,"<100",B1:B100)+SUMIF(A1:A100,"<100",C1:C100) or =3DSUMPRODUCT((A1:A100<100)*(B1:C100)) ...

Read all Fileds/Columns from an CRM Object
Hallo NG, I have a little porblem with my Application. I try to Show an CheckBoxList with all Columns, which exist in the Object CRM Account or CRM Contact. This is very easy as long the CRM only have standard fileds like name, accountnumber or something. But when the CRM have fileds like CFSManager, I can't use this stanard schema, because this filed also schould be show. So is there any possibility, to get the all fileds, which an CRM Object has via Web Serivce or Database? Thanks for help, Andre Grumbach Andre, The p_GetEntityAttributes 'entity' stored procedure (in the Met...

Wrong result is coming while adding tow figures
Hi all, I wrote the following function . But in some time the result is showing wrong format. When VarSum gets value 185.42 and ValueSum gets -185.42 the result should be 0. VarSum = VarSum + ValueSum 0= 185 + - 185 . But some cases Excel shows the balance as 8.5265128E-14. Please help. I given the function below. Sub CashBookMacrosnew() Dim VarSum As Double Dim ValueSum As Double Dim VarSumOld As Double Dim totalsum As Double lastrow = Range("B" & Rows.Count).End(xlUp).Row currow = ActiveCell.Row pre_row = ActiveCell.Row Col_A = "" ...

Adding senders to block list
Under Actions -> Junk Email there is a button to "Add Sender's Domain to Safe Senders Lists" Does anyone know if there is a way to add the senders domain to the blocked senders list. Right now I just go into the blocked senders list and edit the email address to block the domain. Or should this be a suggestion to Microsoft. Thanks ...

Exchange Tabs in AD Users and Computers
Hi, What do I need to install on my XP Pro workstation so that when I pull up the Active Directory Users and Computers, I can see the Exchange Tabs as well. By the way, this is for administering Exchange Server 2000. Thanks. Sam Have you tried installing Exchange System Manager? "Sam" <sam@globalwebcentral.com> wrote in message news:Oalr5s4YEHA.2516@TK2MSFTNGP10.phx.gbl... > Hi, > > What do I need to install on my XP Pro workstation so that when I pull up > the Active Directory Users and Computers, I can see the Exchange Tabs as > well. By the way, this is ...