8 Replies Latest reply: Nov 27, 2012 5:02 AM by Rhuann Joss
Rhuann Joss Level 1 Level 1 (0 points)

I'm constructing a spreadsheet to show the holidays planned and sick days, taken in a small company (14 staff).  I have 12 sheets - one per month, with each name along the top and the days of the month to the left.  I have managed to do the formulas to show 'Holidays' 'Half Days' and 'Sick'.  I also have 2 additional sheets - one for holiday totals and one for sick totals and would like to know how to link the monthly sheets with the totals sheets at the end.  I'm afraid my knowledge of formulas and functions is very basic, so I would like someone to describe in very simple terms how I should go about this!.  Many thanks


MacBook Pro, Mac OS X (10.6.8)
  • Wayne Contello Level 6 Level 6 (15,480 points)

    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.

  • THUSP Level 1 Level 1 (0 points)

    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.

  • Rhuann Joss Level 1 Level 1 (0 points)

    This is one of the 12 monthly sheets

    April

     

    Mary

    Joe

    Fred

    Walter

    Shaney

    Kitty

    Zoe

    Melanie

    Abigail

    Suzanna

    Elaine

    Louise

     

    1

     

     

     

     

     

     

     

     

     

     

     

     

     

    2

     

     

     

     

     

     

     

     

     

     

     

     

     

    3

     

     

     

     

     

     

     

     

     

     

     

     

     

    4

     

     

     

     

     

     

     

     

     

     

     

     

     

    5

    Holiday

     

     

     

     

     

     

     

     

     

     

     

     

    6

     

     

     

     

     

     

     

     

     

     

     

     

     

    7

     

     

     

     

     

     

     

     

     

     

     

     

     

    8

     

    Holiday

     

     

     

     

     

     

     

     

     

     

     

     

    9

     

    Holiday

     

     

     

     

     

     

     

     

     

     

     

     

    10

     

    Holiday

     

     

     

     

     

     

     

     

     

     

     

     

    11

     

     

     

     

     

     

     

     

     

     

     

     

     

    12

     

     

     

     

     

     

     

     

     

     

     

     

     

    13

     

     

     

     

     

     

     

     

     

     

     

     

     

    14

     

     

     

     

     

     

     

     

     

     

     

     

     

    15

     

     

     

     

     

     

     

     

     

     

     

     

     

    16

     

     

     

     

     

     

     

     

     

     

     

     

     

    17

     

     

     

     

     

     

     

     

     

     

     

     

     

    18

     

     

     

     

     

     

     

     

     

     

     

     

     

    19

     

     

     

     

     

     

     

     

     

     

     

     

     

    20

     

     

     

     

     

     

     

     

     

     

     

     

     

    21

     

     

     

     

     

     

     

     

     

     

     

     

     

    22

     

     

     

     

     

     

     

     

     

     

     

     

     

    23

     

     

     

     

     

     

     

     

     

     

     

     

     

    24

     

     

     

     

     

     

     

     

     

     

     

     

     

    25

    SICK

     

     

     

     

     

     

     

     

     

     

     

     

    26

     

     

     

     

     

     

     

     

     

     

     

     

     

    27

     

     

     

     

     

     

     

     

     

     

     

     

     

    28

     

     

     

     

     

     

     

     

     

     

     

     

     

    29

     

     

     

     

     

     

     

     

     

     

     

     

     

    30

     

     

     

     

     

     

     

     

     

     

     

     

     

    HALF DAY

     

     

     

     

     

     

     

     

     

     

     

     

     

    FULL DAY

     

     

     

     

     

     

     

     

     

     

     

     

     

    TOTAL

     

     

     

     

     

     

     

     

     

     

     

     

     

    SICK DAYS

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    This is the totals sheet

    HOLIDAYS

    Mary

    Joe

     

     

     

     

     

     

     

     

     

     

     

    JAN

     

     

     

     

     

     

     

     

     

     

     

     

     

    FEB

     

     

     

     

     

     

     

     

     

     

     

     

     

    MAR

     

     

     

     

     

     

     

     

     

     

     

     

     

    APR

     

     

     

     

     

     

     

     

     

     

     

     

     

    MAY

     

     

     

     

     

     

     

     

     

     

     

     

     

    JUN

     

     

     

     

     

     

     

     

     

     

     

     

     

    JUL

     

     

     

     

     

     

     

     

     

     

     

     

     

    AUG

     

     

     

     

     

     

     

     

     

     

     

     

     

    SEPT

     

     

     

     

     

     

     

     

     

     

     

     

     

    OCT

     

     

     

     

     

     

     

     

     

     

     

     

     

    NOV

     

     

     

     

     

     

     

     

     

     

     

     

     

    DEC

     

     

     

     

     

     

     

     

     

     

     

     

     

    TOTAL

     

     

     

     

     

     

     

     

     

     

     

     

     

    ENTITLEMENT

    28

    28

    28

    28

    28

    28

    28

    28

    28

    28

    28

    28

    28

    Balance Remaining

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    SICK DAYS

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Thank you I really appreciate your help.

  • Wayne Contello Level 6 Level 6 (15,480 points)

    Rhuann,

     

    I received an email from so I am going to post the tables you sent with some questions back here.  Is that OK?

  • Wayne Contello Level 6 Level 6 (15,480 points)

    OK here is the first question:

    Screen Shot 2012-11-19 at 1.44.43 PM.png

     

    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?

  • Rhuann Joss Level 1 Level 1 (0 points)

    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?

  • Wayne Contello Level 6 Level 6 (15,480 points)

    Rhuann,

     

    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):

     

    Screen Shot 2012-11-21 at 9.25.22 PM.png

     

    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

     

    B35=COUNTIF(B4:B34,"*Half Day")

    B36=COUNTIF(B4:B34,"*Half Day")

    B37=B35/2+B36

    B38=COUNTIF(B4:B34, "*sick")

     

    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:

    Screen Shot 2012-11-21 at 9.33.31 PM.png

     

    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

     

    B14=SUM(B2:B13)

    B15  contains 28

    B16=B15-B14

     

    select B14 thru B16 and fill to the right

     

    I hope this helps

     

     

    Regards,

    Wayne

  • Rhuann Joss Level 1 Level 1 (0 points)

    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!!

     

    Kindest regards

    Rhuann