how do I update automatically other worksheets in a file

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