Currently Being ModeratedNov 19, 2012 5:36 AM (in response to Rhuann Joss)
Please post an image of your inputs for one month and what you want the summary sheets to look like so that there are fewer changes once we do post suggestions.
Currently Being ModeratedNov 19, 2012 5:46 AM (in response to Rhuann Joss)
Go to a sheet containing summaries.
Double tap on a cell where sums are to be displayed.
Tap on '='. On the right side you will see a small symbol containing a 'Down arrow'.
It's to choose one of your other sheets.
Use SUM to summarise cells from other sheets.
Currently Being ModeratedNov 19, 2012 6:27 AM (in response to Wayne Contello)
This is one of the 12 monthly sheets
This is the totals sheet
Thank you I really appreciate your help.
Currently Being ModeratedNov 19, 2012 9:26 AM (in response to Rhuann Joss)
I received an email from so I am going to post the tables you sent with some questions back here. Is that OK?
Currently Being ModeratedNov 19, 2012 11:47 AM (in response to Wayne Contello)
OK here is the first question:
1) what is supposed to be in the row for "HALF DAY"?
2) what is supposed to be in the row for "FULL DAY"?
3) what is supposed to be in the row for "TOTAL"?
4) just to confirm... the row for "SICK DAYS" is to count the cells in the same column marks with the text "SICK", right?
Currently Being ModeratedNov 21, 2012 7:39 AM (in response to Wayne Contello)
The formula for
half days is =COUNTIF(A2:A31,"*Half Day")
Full day is =COUNTIF(A2:A31,"*Full Day")
TOTAL is =SUM(MaryHalf Day /2+Mary Full Day)
Sick 12 =COUNTIF(A2:A31,"*Sick")
Does that all make sense?
Currently Being ModeratedNov 21, 2012 7:35 PM (in response to Rhuann Joss)
Here is one possible solution...
I am making the following assumptions:
1) Each month will have an individual table (for a total of 12 in a year)
2) there will be a summary table for each year
Here is what a monthly table looks like (using the information you provided):
for this table the formulas are:
A4=IF((DATE($B$1, $B$2, 1) +(ROW()-3))<= EOMONTH(DATE($B$1, $B$2, 1),0), DAY(DATE($B$1, $B$2, ROW()-3)), "")
select A4 and fill down to A34 (for a maximum of 31 days in a month)
you enter the year and month in cells B1 and B2
select B35 thru B38 and fill to the right
Now duplicate the whole table by selecting the table then select the menu item "Edit > Duplicate"
update cells B1 and B2 with the year and month (let's assume "2" for "February")
Now the summary table:
B2=IFERROR(INDIRECT("Monthly Data :: "&$A2&" :: "& CHAR(CODE("B")+(COLUMN()-2))&"38"), "")
select B2 , then fill to the right as needed
then select B2 thru the end of the row (or how ever far you previously filled)
now fill down for twelve months
B15 contains 28
select B14 thru B16 and fill to the right
I hope this helps
Currently Being ModeratedNov 27, 2012 5:02 AM (in response to Wayne Contello)
Thank you so much Wayne, apologies for delay in replying!! As a 60 year old former primary school teacher in a completely new industry, it's been quite a challenge getting my head around this and I certainly wouldn't have done without your help!! What a star, thanks again!!