repost; Automatically updating formulae in multiple sheets
 

I list sales of 20+ products each week for the year
A2-A23 are the products and B-BA are the colums for each week
I have multiple sheets that have the same sales results in the same format
foreach previous year 2004;2003;2002;2001;2000
Each week I want to compare total sales in the year to date i.e weeks 1-20
with weeks 1-20 in each previous year. so next week it will be 1-21.
I have on each sheet a cumulative total for each of the 20 products in the
year to date i.e Cell A25=SUMB2:BA2 and Cell A26=SUMB3:BA3 and so on for
each product. This means each week I have to alter the formula in Cell A25 to
the right number of weeks and then copy& paste down for all 20 products. On
each of the previous year sheets I have to do the same. Is there a
formula/instruction I can put into Cell A25 in the 2004 sheet that will
change its reference to the same range of cells as I have put into the 2005
sheet. Can I then cascade that down to the sheets for 2003, 2002 etc? If this
possible it would save me a lot of work each week.

repost; Automatically updating formulae in multiple sheets
 

Send me the workbook and this information you wrote here. Also more info as
you think of it.



No guarantees of success though...

http://www.geocities.com/excelmarksway



"Keith Nicholls" wrote:

I list sales of 20+ products each week for the year
A2-A23 are the products and B-BA are the colums for each week
I have multiple sheets that have the same sales results in the same format
foreach previous year 2004;2003;2002;2001;2000
Each week I want to compare total sales in the year to date i.e weeks 1-20
with weeks 1-20 in each previous year. so next week it will be 1-21.
I have on each sheet a cumulative total for each of the 20 products in the
year to date i.e Cell A25=SUMB2:BA2 and Cell A26=SUMB3:BA3 and so on for
each product. This means each week I have to alter the formula in Cell A25 to
the right number of weeks and then copy& paste down for all 20 products. On
each of the previous year sheets I have to do the same. Is there a
formula/instruction I can put into Cell A25 in the 2004 sheet that will
change its reference to the same range of cells as I have put into the 2005
sheet. Can I then cascade that down to the sheets for 2003, 2002 etc? If this
possible it would save me a lot of work each week.