Holiday spreadsheet

[tammysula]

Hi All

I am hoping you can help me.
I have to produce a holiday calender/sick calender for staff of 28.

They can book half day am, halfday pm, full day, and there is sick am, sick pm, sick full day.

so against each date i will enter HAM or HPM or HFD, same with the sick row SAM, SPM, SFD.

I have set up auto conditional formating so that when either of the words entered below is added it colours the box.

the next step I can not seem to figure out.

in the total column i need it to:

each person is a row. the row of cells C7:AG7. if it has HAM or HPM in it, i need it to assign these words the value of 0.5 (half day), i also need it to HFD assign a value of 1, and then add them together to give me total number of days used in the month.

can this be done??

Been trying for a week now qand at my wits end because nothing works.

PS I am a novice with excel.

 

[FormR]

in the total column i need it to:

each person is a row. the row of cells C7:AG7. if it has HAM or HPM in it, i need it to assign these words the value of 0.5 (half day), i also need it to HFD assign a value of 1, and then add them together to give me total number of days used in the month.

Hi, welcome to the forum! You can try this:

=SUMPRODUCT(COUNTIFS(C7:AG7,{"HAM","HPM","HFD"}),{0.5,0.5,1})

 

[tammysula]

Hi, welcome to the forum! You can try this:

=SUMPRODUCT(COUNTIFS(C7:AG7,{"HAM","HPM","HFD"}),{0.5,0.5,1})

Can I say I love you!!!! Omg it worked. I can sleep tonight.

so I have set it all up. And then we have a new starter. I need to insert his row. But I have a total page that's already linked to the monthly sheets.

If if I insert a row will it throw everything off on the total page. Can I somehow freeze the or something so if it all moves down. Not sure if this makes any sense at all.

 

[tammysula]

Hi

Update spreadsheet is up and running, thank you so much.

next question. On the first page i have all the staff listed, 28 of them, on the next 12 sheets (months) i have all the staff listed in the same order.

is there a way I can click on the name on the front sheet, and it will somehow give me the a "filter" for that person on the next 12 sheets, so I can just click from a drop down and it will take me to that person on that sheet, or am I exppecting miracles!!

Tammy