software

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 amp;
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
quot;Keith Nichollsquot; wrote:

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

quot;CLRquot; wrote:

gt; In any cell on any worksheet you can enter this formula and it will return
gt; the contents of Cell A1 from Sheet 1.
gt;
gt; =Sheet1!A1
gt;
gt; Change the SheetName and Cell address to suit your needs.
gt;
gt; hth
gt; Vaya con Dios,
gt; Chuck, CABGx3
gt;
gt;
gt;
gt; quot;Keith Nichollsquot; wrote:
gt;
gt; gt; Each week I enter sales 4 products in sheet1.With a column per week and row
gt; gt; per product. Below this I have a cumulative total 2date for each product.
gt; gt; Sheets 2-6 have results 4 each of the previous 5 years and also a similar
gt; gt; table showing the cumulative total 2date.Sheets 7 -11 compare performance
gt; gt; between successive years.Sheet,12, shows results 2date for each product amp;
gt; gt; each of the 6 years.
gt; gt; At present I enter current figures in sheet 1,then go to sheets2-6 and alter
gt; gt; the sum formula for each row to cover the same number of weeks as on sheet 1.
gt; gt; Simlarly I have to go to the comparisions sheets 7-11 to change the sum
gt; gt; formula there. Is there a way I can alter the row sum formula on sheet 1 and
gt; gt; it automatically alter the corresponding formula on all the other sheets?
gt; gt; 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
quot;Keith Nichollsquot; wrote:

gt; Thanks for suggestion but unforunately it does not solve problem. If I am in
gt; week20 of the 2005 the cumulative figure in my summary is the sum of cells
gt; a1: a20 on sheet 1 and so on down the 50 products I monitor.
gt; On sheet 2004 my summary is the same product a1:a20 and so on for the five
gt; other sheets
gt; What I am trying to do is find a way so that when i get to Week 21 and I
gt; change the formula in the summary to a1:a21 it will automatically change the
gt; formula on Sheet 2004 to a1:a21 and so on through the other sheets. At the
gt; moment I am changing the formula on the first line of the summary and then
gt; copy/pasting the change down all fifty products and then repeating the proces
gt; on each of the other 10 pages.
gt; It would be great to find a way of doing only the current year sheet and
gt; having all the other sheets update formulas at the same time
gt;
gt; quot;CLRquot; wrote:
gt;
gt; gt; In any cell on any worksheet you can enter this formula and it will return
gt; gt; the contents of Cell A1 from Sheet 1.
gt; gt;
gt; gt; =Sheet1!A1
gt; gt;
gt; gt; Change the SheetName and Cell address to suit your needs.
gt; gt;
gt; gt; hth
gt; gt; Vaya con Dios,
gt; gt; Chuck, CABGx3
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;Keith Nichollsquot; wrote:
gt; gt;
gt; gt; gt; Each week I enter sales 4 products in sheet1.With a column per week and row
gt; gt; gt; per product. Below this I have a cumulative total 2date for each product.
gt; gt; gt; Sheets 2-6 have results 4 each of the previous 5 years and also a similar
gt; gt; gt; table showing the cumulative total 2date.Sheets 7 -11 compare performance
gt; gt; gt; between successive years.Sheet,12, shows results 2date for each product amp;
gt; gt; gt; each of the 6 years.
gt; gt; gt; At present I enter current figures in sheet 1,then go to sheets2-6 and alter
gt; gt; gt; the sum formula for each row to cover the same number of weeks as on sheet 1.
gt; gt; gt; Simlarly I have to go to the comparisions sheets 7-11 to change the sum
gt; gt; gt; formula there. Is there a way I can alter the row sum formula on sheet 1 and
gt; gt; gt; it automatically alter the corresponding formula on all the other sheets?
gt; gt; gt; 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