Each week I enter sales 4 products in sheet1.With a column per week and row
per product. Below this I have a cumulative total 2date for each product.
Sheets 2-6 have results 4 each of the previous 5 years and also a similar
table showing the cumulative total 2date.Sheets 7 -11 compare performance
between successive years.Sheet,12, shows results 2date for each product &
each of the 6 years.
At present I enter current figures in sheet 1,then go to sheets2-6 and alter
the sum formula for each row to cover the same number of weeks as on sheet 1.
Simlarly I have to go to the comparisions sheets 7-11 to change the sum
formula there. Is there a way I can alter the row sum formula on sheet 1 and
it automatically alter the corresponding formula on all the other sheets?
save me a lot of time!

In any cell on any worksheet you can enter this formula and it will return
the contents of Cell A1 from Sheet 1.

=Sheet1!A1

Change the SheetName and Cell address to suit your needs.

hth
Vaya con Dios,
Chuck, CABGx3



"Keith Nicholls" wrote:

Each week I enter sales 4 products in sheet1.With a column per week and row
per product. Below this I have a cumulative total 2date for each product.
Sheets 2-6 have results 4 each of the previous 5 years and also a similar
table showing the cumulative total 2date.Sheets 7 -11 compare performance
between successive years.Sheet,12, shows results 2date for each product &
each of the 6 years.
At present I enter current figures in sheet 1,then go to sheets2-6 and alter
the sum formula for each row to cover the same number of weeks as on sheet 1.
Simlarly I have to go to the comparisions sheets 7-11 to change the sum
formula there. Is there a way I can alter the row sum formula on sheet 1 and
it automatically alter the corresponding formula on all the other sheets?
save me a lot of time!

Thanks for suggestion but unforunately it does not solve problem. If I am in
week20 of the 2005 the cumulative figure in my summary is the sum of cells
a1: a20 on sheet 1 and so on down the 50 products I monitor.
On sheet 2004 my summary is the same product a1:a20 and so on for the five
other sheets
What I am trying to do is find a way so that when i get to Week 21 and I
change the formula in the summary to a1:a21 it will automatically change the
formula on Sheet 2004 to a1:a21 and so on through the other sheets. At the
moment I am changing the formula on the first line of the summary and then
copy/pasting the change down all fifty products and then repeating the proces
on each of the other 10 pages.
It would be great to find a way of doing only the current year sheet and
having all the other sheets update formulas at the same time

"CLR" wrote:

In any cell on any worksheet you can enter this formula and it will return
the contents of Cell A1 from Sheet 1.

=Sheet1!A1

Change the SheetName and Cell address to suit your needs.

hth
Vaya con Dios,
Chuck, CABGx3



"Keith Nicholls" wrote:

Each week I enter sales 4 products in sheet1.With a column per week and row
per product. Below this I have a cumulative total 2date for each product.
Sheets 2-6 have results 4 each of the previous 5 years and also a similar
table showing the cumulative total 2date.Sheets 7 -11 compare performance
between successive years.Sheet,12, shows results 2date for each product &
each of the 6 years.
At present I enter current figures in sheet 1,then go to sheets2-6 and alter
the sum formula for each row to cover the same number of weeks as on sheet 1.
Simlarly I have to go to the comparisions sheets 7-11 to change the sum
formula there. Is there a way I can alter the row sum formula on sheet 1 and
it automatically alter the corresponding formula on all the other sheets?
save me a lot of time!

If you put this formula in any cell of any sheet, it will SUM the contents of
cells A1 through A52 on Sheet 1............

=SUM(Sheet1!A1:Sheet1!A52)

hth
Vaya con Dios,
Chuck, CABGx3



"Keith Nicholls" wrote:

Thanks for suggestion but unforunately it does not solve problem. If I am in
week20 of the 2005 the cumulative figure in my summary is the sum of cells
a1: a20 on sheet 1 and so on down the 50 products I monitor.
On sheet 2004 my summary is the same product a1:a20 and so on for the five
other sheets
What I am trying to do is find a way so that when i get to Week 21 and I
change the formula in the summary to a1:a21 it will automatically change the
formula on Sheet 2004 to a1:a21 and so on through the other sheets. At the
moment I am changing the formula on the first line of the summary and then
copy/pasting the change down all fifty products and then repeating the proces
on each of the other 10 pages.
It would be great to find a way of doing only the current year sheet and
having all the other sheets update formulas at the same time

"CLR" wrote:

In any cell on any worksheet you can enter this formula and it will return
the contents of Cell A1 from Sheet 1.

=Sheet1!A1

Change the SheetName and Cell address to suit your needs.

hth
Vaya con Dios,
Chuck, CABGx3



"Keith Nicholls" wrote:

Each week I enter sales 4 products in sheet1.With a column per week and row
per product. Below this I have a cumulative total 2date for each product.
Sheets 2-6 have results 4 each of the previous 5 years and also a similar
table showing the cumulative total 2date.Sheets 7 -11 compare performance
between successive years.Sheet,12, shows results 2date for each product &
each of the 6 years.
At present I enter current figures in sheet 1,then go to sheets2-6 and alter
the sum formula for each row to cover the same number of weeks as on sheet 1.
Simlarly I have to go to the comparisions sheets 7-11 to change the sum
formula there. Is there a way I can alter the row sum formula on sheet 1 and
it automatically alter the corresponding formula on all the other sheets?
save me a lot of time!

Why not put your cumulative total2date at the top of your sheets 2-6
in row 1 (insert a blank row here to move the other rows down) and make
your sum range fixed as A3:A53 to automatically cover the 50 rows in
sheet 1 you will need. This way you won't have to make any adjustments.

Pete