roster

Hi Im looking for some help, I will try describe what im trying to achieve.

I am trying to create a roster that auto calculates hours worked.

for example cell a1 = 0800 - 2000
a2 = workers name

Is there anyway that when I type in the workers name it automatically adds 12 hours to his name on a second sheet. Not sure if this can be done and how complicated it would be, but im eventually looking to do this for about 50 employees.

Any help appreciated

Cheers

I have attached my roster to give a better idea of how it looks,

cheers

You could list all the employees names on the second sheet and then use the CountIf function to count the number of times each name appears on the first sheet multiplied by 12 to give the hours.

eg if the names are listed in column A of Sheet 2, you would use in Sheet 2 cell B2

=COUNTIF(Sheet1!$B$12:$H$115,Sheet2!A2)*12

This formula can be copied down column B and will automatically update as you add the names to Sheet 1

Cheers for that nice idea, only problem left is that shift hours vary between 12 - 16 hours. Any ideas?

cheers

If you don't want to use vba, you could add extra columns to show the hours for each time period using the formula

Please Login or Register  to view this content.

and then using the SumIf function in Sheet 2

=SUMIF(Sheet1!$B$11:$N$117,Sheet2!A2,nSheet1!$C$11:$O$117)

See the attached file.

Your a genius, works a treat just what I needed.

Cant thank you enough.

Cheers :D

Hey Folks,

Spreadsheet is working a treat, thanks for that, Ive added in a drop down list for to select person working that shift. Was wanting to try and take it a bit further.

i.e When person is selected for shift they no longer appear in the drop down list for that particular day, this basically so that same person cannot be chosen for 2 shifts on the same day.

Any help or suggestions would be great.

Cheers

You could use conditional formatting to highlight when a person appears more than once on the same day.

See attached file.

Thanks for this, it will certainly help but is there any way I can get the names to disapear from list when used once in that columb. Im thinking if the sheet is near full and only a couple of slots left It would be good if the list just showed the people available.

Any Ideas

Cheers

Managed to get this working using this method on this site http://www.contextures.com/xlDataVal03.html.

All I need to figure out now is for the names not to show on the next day if they were night shift the day before.

Any Ideas.

Here is the most recent and up to date sheet.

Cheers

Should have described my previous post a bit better, Name to be deleted from next day if day shift is selected but to remain if night shift is selected.

Al help is apprieciated

Cheers