Formula for Increasing a Cell Reference by a Given Number
Looking for help as to whether or not there is a formula for increasing a
cell range by a predetermined number. My example - measuring sales results weekly on a spreadsheet by referencing a more complex spreadsheet where the numbers are hard coded in. For week one, the reference is =SUM('January 2007'!$B3:$B9) - for the next week I want to increase the cell reference by seven days i.e. =SUM('January 2007'!$B10:$B16) i was hoping to avoid entering the formula in manually or selecting the cells by entering "=" and then manually going to the more complex spreadsheet and selecting the cells. was hoping to enter the formula and copy cells down the column? any help would be appreciated |
Formula for Increasing a Cell Reference by a Given Number
Dynamic named ranges are your friend he
http://www.ozgrid.com/Excel/DynamicRanges.htm -- Brevity is the soul of wit. "Kentle" wrote: Looking for help as to whether or not there is a formula for increasing a cell range by a predetermined number. My example - measuring sales results weekly on a spreadsheet by referencing a more complex spreadsheet where the numbers are hard coded in. For week one, the reference is =SUM('January 2007'!$B3:$B9) - for the next week I want to increase the cell reference by seven days i.e. =SUM('January 2007'!$B10:$B16) i was hoping to avoid entering the formula in manually or selecting the cells by entering "=" and then manually going to the more complex spreadsheet and selecting the cells. was hoping to enter the formula and copy cells down the column? any help would be appreciated |
Formula for Increasing a Cell Reference by a Given Number
You could try something like this:
=SUM(INDIRECT("'January 2007'!$B"&(ROW()*7-4)&":$B"&(ROW()*7+2))) This assumes your formula is placed in Row 1. If it is placed in a different row, then just adjust the -4 and +2 accordingly. HTH, Elkar "Kentle" wrote: Looking for help as to whether or not there is a formula for increasing a cell range by a predetermined number. My example - measuring sales results weekly on a spreadsheet by referencing a more complex spreadsheet where the numbers are hard coded in. For week one, the reference is =SUM('January 2007'!$B3:$B9) - for the next week I want to increase the cell reference by seven days i.e. =SUM('January 2007'!$B10:$B16) i was hoping to avoid entering the formula in manually or selecting the cells by entering "=" and then manually going to the more complex spreadsheet and selecting the cells. was hoping to enter the formula and copy cells down the column? any help would be appreciated |
Formula for Increasing a Cell Reference by a Given Number
Kentle....I think for money matters...its better to prepare first your
worksheet with named ranges (weeks, months, years) where one day = one row....your cross-checking of formula and highlighting will be more easy.... "Elkar" wrote: You could try something like this: =SUM(INDIRECT("'January 2007'!$B"&(ROW()*7-4)&":$B"&(ROW()*7+2))) This assumes your formula is placed in Row 1. If it is placed in a different row, then just adjust the -4 and +2 accordingly. HTH, Elkar "Kentle" wrote: Looking for help as to whether or not there is a formula for increasing a cell range by a predetermined number. My example - measuring sales results weekly on a spreadsheet by referencing a more complex spreadsheet where the numbers are hard coded in. For week one, the reference is =SUM('January 2007'!$B3:$B9) - for the next week I want to increase the cell reference by seven days i.e. =SUM('January 2007'!$B10:$B16) i was hoping to avoid entering the formula in manually or selecting the cells by entering "=" and then manually going to the more complex spreadsheet and selecting the cells. was hoping to enter the formula and copy cells down the column? any help would be appreciated |
All times are GMT +1. The time now is 01:01 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com