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

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

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

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