using insert with a formula
Posted by casey frankenberger on February 09, 2002 4:18 PM
Hello,
Im wondering if its possible to and if theres a simple way to deal with this problem. What my dilema is is that my boss wants to create weekly reports that take up 4 columns and various cells hold different info. each week he wants to insert a new report into the same spreadsheet, so that he has a history of these reports starting with the most recent toward the left and the oldest to the right. So far no a problem, but he wants to have report which sums all of the individual report cells and when he wants to insert a report from new week it would get included into the sum. I dont know if Ive made myself clear, If not ill try to simplfy my question. let me know of any ways you could suggest I could do this.
Thanks
Casey
Posted by Rod on February 09, 2002 4:37 PM
I cant tell for sure which way you are adding up, rows or columns. But it appears you want to total horizontal if so here is one solution.
Insert two blank columns before the data, (Col A & Col B)
Make your sum formula sum from Column B to your last column of data. Hide column B so that when you insert new columns, you will be inserting from Column C. Then you sum formulas will include any new columns that you instert.
Hope this helps.
Rod
Posted by Mark W. on February 09, 2002 4:42 PM
In addition to your Summary Worksheet add 2 more
worksheets between which all of your weekly
sheets should be placed. Supposed you used
sheets named --> and <-- as your "boundary"
sheets... then the formula, =SUM('-->:<--'!A1),
entered in you Summary sheet would sum cell A1 of
any and all weekly sheets. These boundary sheets
could be named Begin and End... or anything you
like.
Posted by casey frankenberger on February 09, 2002 6:00 PM
Yes im adding horizontally, but im not sure I understand. essentially I want to sum every 4th cell across the row into the first cell, and if I insert another 4 columns into this the fourth cell which resides in the fourth column would be part of the formula.
Thanks
Casey
Posted by Rod on February 09, 2002 11:56 PM
I think I see what you are looking for. Try this, As I said before, hide Column B, and Enter this formula in Column A. (Using row 7 for example)
=SUM(IF(MOD(COLUMN(B6:IV6)-2,4)=0,B7:IV7))
This is an Array "CSE" Formula so after you type this formula in, dont press enter you must press ctl+shft+Enter. If you do the formula should look like this
{=SUM(IF(MOD(COLUMN(B6:IV6)-2,4)=0,B7:IV7))}
This formula will sum every fourth column starting from column C. As you insert columns your formulas will update automaticly.
For more help on CSE formulas, see
http://www.mrexcel.com/tip011.shtml
