Formula for Increasing a Cell Reference by a Given Number

[Kentle]

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

 

[Dave F]

Dynamic named ranges are your friend here:
http://www.ozgrid.com/Excel/DynamicRanges.htm

 

[Elkar]

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

 

[romelsb]

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....