Adding cell contents in different worksheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Im creating a computerised rota system for my job which has 52 worksheets in
the workbook. On each worksheet will be a "Tax To Date" cell where the total
tax paid to date will be entered. Does anyone know how i can take the number
from, say, cell A2(worksheet1) and add it to the cell A1(worksheet2) and
display the result in cell A2(worksheet2) and so on through the 52 worksheets.
Each worksheet is renamed 1 to 52 and each worksheet is currently identical
except the values to be inserted.
Any help on this would be great and could you please simplify any formulas
because im not that bright, ha ha. Thanks.
 
Hi

The results of several sheets can be added together by using the formula
=SUM(1:52!A2) and this would add all of the values for the whole year.
My guess is that you are using the sum of Tax paid up to previous week
to deduct from cumulative tax due to arrive at tax due for this week.
Even though, if you are in say week 3, summing the whole year will not
matter, as weeks 4 through 52 will be blank, you will get a circular
reference error when trying to update cell A2 for that week.

This could be overcome provided you have a week number held somewhere on
the sheet, say in cell A1. The formula would then be
=SUM(INDIRECT("1;"&(MAX(1,A1-1))&"!"&A2)
My own preference in this scenario would be to have a blank sheet called
YTD and to drag this to a position just before the current weeks sheet
that I am working on, and change the formula to
=SUM(1:YTD!A2)
 
I have not got a clue what all that formlua was, last time i saw something
like that was at school, ha ha, but ive managed to do it from what i could
make out.

One more thing, is there a formula to do all 52 sheets in one instead of
doing the same procedure 52 times for tax and another 52 times for insurance?
 
Hi

Click on the first sheet tab, hold down the shift button and click on
the last sheet tab and that will Group the sheets and you will see the
word Group at the top of the screen.
Any changes made on any sheet will be replicated on all of them.
Click on any sheet tab not included in the group (or on any sheet tab
within the group if they are all selected) and that will ungroup them.
 
Back
Top