Schedule Sheet: Cells That Holding Values.

**Dunda** · 08-08-2009, 12:28 AM

Hey everyone,

This is going to be my first post so bare with me!

I've got an idea for a work scheduling sheet but can't quite figure out what forumula to use and how to apply it.

I'll be as descriptive as possbile. A table with employee names along the right side, days along the top, total hours along left side. As you will understand, this results in a cell for each person per day. In that cell I should be able to schedule a shift, let it be an 'A' or a 'B' shift.

Shift 'A' is 11.0 hours.
Shift 'B' is 12.5 hours.

Example: If I'd schedule a given person two 'A' shifts, it should add up those 'A's' and display a total off 22.0 hours along the left side.

When I print of the sheet I want it to print just the names, days with shifts accordingly but leaving out the total amount of hours. (I want that purely as a reference for myself while making up a schedule, this to stay within a certain amount of hours per pay period).

It shouldn't be to hard I imagine but 'how' is the question. Hopefully someone can help me out, I'd really appreciate it.

Thanks in advance,

Daniel

**DonkeyOte** · 08-08-2009, 01:46 AM

Daniel, without specifying any specific ranges it's hard for us to give precise answers but let's say Total Hours are to go in A2 downwards, days are listed say in B1:Y1 with names in Z2 downwards... the A/B are thus entered into the matrix between B2:Yx where x is last row. Now if your options are as you say they are, fixed, then:

A2: =IF($Z2="","",SUM(COUNTIF($B2:$Y2,{"A","B"})*{11,12.5}))
copied down for all rows

If you want A2 in time format you can alter 11,12.5 to "11:00", "12:30" remembering to format cell (A2 down) to use custom format of [h]:mm

If your list of shifts is more expansive than you suggest and/or you want to be alter the time associated quickly then we would probably adopt a slightly different approach - let us know - and if nec. please post a sample workbook so we can see with what exactly you are working with.

**Dunda** · 08-08-2009, 02:17 AM

Hey DonkeyOte,

Thanks for the tips but yes, indeed. The shift times change from time to time, they are a variable.

The attached scheduling 'template' is what I got from someone else. I'd like to redo it by starting over fresh, doing so in an organized, clean looking and if at all possible, simplified way.

Thanks again,

Daniel

**DonkeyOte** · 08-08-2009, 02:39 AM

Daniel, I would suggest posting a file that demonstrates your desired output, it need not be extensive - just a general idea of your ideal "final layout" ... it sounds as though you have an idea already... we can give you the formulae once we know what you're looking to populate.

**Dunda** · 08-08-2009, 03:14 AM

Alrighty

This would be the schedule as to how I would like it to look with some criteria that I'd like the sheet to meet. Hope this helps a bit more.

Daniel

**DonkeyOte** · 08-08-2009, 03:59 AM

perhaps something along the lines of the attached:

Note however:

1. Shifts A, B, C and D are subject to change. (Start and finishing times change with time and will need to be revised frequently).

If you're changing the hours for each shift you will have issues with historics becoming restated at the latest rate... you may need to think of a more sophisticated approach which has multiple columns per shift, headed perhaps by an effective date ... this would in turn require your entry sheet to become slightly more sophisticated such that each column is a real date value (ie B4:AC4)

**Dunda** · 08-08-2009, 03:59 PM

Alright.

Is there a way of linking A3 with B3 on sheet 2?
That way A3 gets B3's value, in which case shift 'A' gets the value '7'.

Then when I put an 'A' in the table on sheet 1, it will show 'A' but holds a value of 7 which can be added under 'Total Hours'.

This way I can change times easily by simply changing B3's value. That will then change in Total Hours as well since it's all linked.

They are real simple formula's but I just don't know which ones to use.