Seniority spreadsheet

**pont463** · 09-27-2011, 09:11 PM

I am trying to build a spreadsheet that allows my employees to pick their shift acording to senoirity. I was starting to seperate it out, but then realized my idea wouldn't work. So now i am asking for your help. I have 18 employees and there are 6 shifts total. 3 men per shift. They get to choose shift by order of preference (i e 1-6) and then are ranked by senoirty to see what shifts they get.if the first shift is full then they go to their second pick right on down the seniority list till all is full. Any help would be greatly appreciated. Thanks in advance.

**Marcol** · 09-28-2011, 08:45 AM

Hi pont463

Welcome to the forum ...

See if this workbook helps any, it's the simplest I can think of without reverting to VBa.

Enter the employees' first preference only, if the shift is not available, on a seniority basis, the cells will turn red.
Data validation will warn you if the shift is already allocated in case you didn't notice the cell was red!

If the cell was valid when the selection was made, and later a more senior employees wants that shift, then the most senior member will be highlighted in the "Selected for Shift" Table, and therefore can be first to be asked his next preference.

The "Schedule" Table will automatically update with this array formula in K25

Please Login or Register  to view this content.

Confirm with Ctrl+Shift+Enter not just enter.
Then Drag across to Column P and Down to Row 27.

Tip:= When you need to return a blank cell with an IF() statement use "" not " " (space)
e.g.
In K3

Please Login or Register  to view this content.

is, generally speaking, better than

Please Login or Register  to view this content.

Hope this helps.

**pont463** · 09-28-2011, 10:55 AM

That is a massive step in the right direction. The problem i see is that you still have to manually look at the order of the employees pics to reclassify them ito 1st picks.. for example tonys first pick was W-S-M.. his second was M-F-D. because his first pick was full his second pick had to be made his first, and from what i see, that still had to be done manually.. isn't there a way to have it do that automatically?

**Marcol** · 09-28-2011, 11:02 AM

I think that will be easiest using VBa rather than native formulae, maybe not.

Can't look at it at the moment, look back later, if nobody else has chipped in, I'll give it another go.

**pont463** · 09-28-2011, 12:14 PM

Ok.. i appreciate all the help i can get.. i must warn you though.. i have no experience with VBa at all so it will be a completely new thing for me..

**ecorona1980** · 08-02-2019, 10:25 PM

good afternoon,

I need to create a seniority schedule spreadsheet. mine is a bit tricky and I'm having a hard time coming up with how to set it up.
my seniority is based on job functions, for example: I have a seniority list of 212 employees. not all employees have the same job function. there are some who do 5 different jobs where as there are some that only do 4,3, etc... we currently make a schedule every day by hand and its not only time consuming but its a headache. my idea is to be able to tell my spreadsheet ok, I need 10 people to work as bulkers and for it to pull the first 10 bulkers on my seniority list, but then I want to tell it ok, now I need 7 diggers that are not on the bulker column and then I want to say now I need 12 toppers that are not on the bulker list nor the digger list can you see the pattern? I really need help.!!

**FDibbins** · 08-03-2019, 12:05 AM

Administrative Note:
ecorona1980
Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread