Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Skipping Columns
There must be a simple solution to this, but I'm having trouble finding
it: I have two spreadsheets. One has daily sales totals that is updated every day. The other references those totals, but adds them together in a cumulative sum. Sheet 1: 6/1 6/2 6/3 5 $10 6 $12 4 $8 On Sheet 2, I'd like to show cumulative totals: 6/1 6/2 6/3 $10 $22 $30 Problem is, on Sheet 1 I have an extra column in between each daily total (quantity, as you can see in my "example") Right now to get the cumulative total for a day I can reference the previous day's cumulative total (in the immediately preceding cell on the current sheet) and adding to it that day's daily total from the other sheet. I can do this manually, but I don't have time to adjust it for an entire month, times twelve different items. Is there a way to adjust my formula so that I can just copy & paste it across my sheet? What I need is a way to skip a column in my formula... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Skipping Columns
Here is one solution. For this example, assume your Sheet1 data is on row 6,
starting in column A. If I understand your description correctly, column A has a quantity, column B has sales $, etc. Therefore, you want a cumulative total of only the even-numbered columns (B, D, F, etc.) 1. In A7 on Sheet1, enter this formula: =IF(MOD(COLUMN(),2)=1,0,A6) and copy it across all your columns of data. You can hide this row if you want. 2. In column A on Sheet2 (6/1 in your example), enter this formula: =SUM(Sheet1!$A7:OFFSET(Sheet1!A7,0,(COLUMN()*2)-COLUMN())) and copy it across. This will give you a cumulative total of only the sales $ columns. If my example is mistaken, and you need to total the odd-numbered columns (A, C, E, etc.) , change =1 in the Sheet1 formula to =0. Hope this helps, Hutch " wrote: There must be a simple solution to this, but I'm having trouble finding it: I have two spreadsheets. One has daily sales totals that is updated every day. The other references those totals, but adds them together in a cumulative sum. Sheet 1: 6/1 6/2 6/3 5 $10 6 $12 4 $8 On Sheet 2, I'd like to show cumulative totals: 6/1 6/2 6/3 $10 $22 $30 Problem is, on Sheet 1 I have an extra column in between each daily total (quantity, as you can see in my "example") Right now to get the cumulative total for a day I can reference the previous day's cumulative total (in the immediately preceding cell on the current sheet) and adding to it that day's daily total from the other sheet. I can do this manually, but I don't have time to adjust it for an entire month, times twelve different items. Is there a way to adjust my formula so that I can just copy & paste it across my sheet? What I need is a way to skip a column in my formula... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Skipping Columns
Here is a different approach. Same assumptions as my previous post, but with
this method, no extra formula is needed on Sheet1. Enter the following array formula in column A (6/1 in your example) on Sheet2, and copy across: =SUM(IF(MOD(COLUMN(Sheet1!$A6:OFFSET(Sheet1!$A6,0, COLUMN()*2)),2)=0,Sheet1!$A6:OFFSET(Sheet1!$A6,0,C OLUMN()*2))) Please note that this is an array formula, so instead of {Enter} you have to press {Ctrl}{Shift}{Enter} to enter it. If you do it correctly, the formula will show curly braces around it (you can't enter the curly braces yourself). You can copy & paste or drag it to other cells normally. Gives the same result as the other approach. Hope this helps, Hutch " wrote: There must be a simple solution to this, but I'm having trouble finding it: I have two spreadsheets. One has daily sales totals that is updated every day. The other references those totals, but adds them together in a cumulative sum. Sheet 1: 6/1 6/2 6/3 5 $10 6 $12 4 $8 On Sheet 2, I'd like to show cumulative totals: 6/1 6/2 6/3 $10 $22 $30 Problem is, on Sheet 1 I have an extra column in between each daily total (quantity, as you can see in my "example") Right now to get the cumulative total for a day I can reference the previous day's cumulative total (in the immediately preceding cell on the current sheet) and adding to it that day's daily total from the other sheet. I can do this manually, but I don't have time to adjust it for an entire month, times twelve different items. Is there a way to adjust my formula so that I can just copy & paste it across my sheet? What I need is a way to skip a column in my formula... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum skipping columns | Excel Discussion (Misc queries) | |||
Summing across columns while skipping some columns | Excel Worksheet Functions | |||
Totals skipping columns | Excel Discussion (Misc queries) | |||
skipping columns part 2 | Excel Worksheet Functions | |||
skipping columns | Excel Worksheet Functions |