I am looking for a simpler way to rotate through a list of employee's based on seniority in an schedule built through excel. Simply stated - we have mandatory overtime, and we mandate by order of seniority, starting with the employee with the least amount of overtime in each shift.
I began the process by creating a never-ending nest of IF's, in excel 2013, but there has to be an easier formula to use!
Thanks!
Hi Jon,
Welcome to our forum. Thanks for your question and sharing your file.
You could try using INDEX & MATCH in cell J59 like so :
=IF(J58<5,
IFERROR(INDEX('Seniority by Shift'!B16:B19,MATCH(I59,'Seniority by Shift'!B16:B19,0)-1),
FERROR(INDEX('Seniority by Shift'!B17:B19,MATCH(H59,'Seniority by Shift'!B17:B19,0)-1),
IFERROR(INDEX('Seniority by Shift'!B18:B19,MATCH(G59,'Seniority by Shift'!B18:B19,0)-1),
INDEX('Seniority by Shift'!B12:B19,8)))))
However, you'd need to modify it for the other columns and therefore it's not ideal because you should aim to have your formulas consistent across the row.
Mynda
Mynda,
That formula doesn't quite work as if someone is mandated to work on the fist day (f59) it doesn't recognize that as a mandate and then mandates that same person on the j59 day. I tried to modify it, but was unable to figure it out. That formula does simplify the language some though. Thank you!
Jon
Hi Jon,
Ah, I see I left out the final IF so you just need to add another IFERROR(INDEX for F59
That said, I don't think trying to nest every criteria in a single formula is the most efficient approach. You should aim to have one formula that you can copy across the row, therefore you probably need to move some of the logic to a helper row (say row 60 that you can hide if you want), which will allow the formulas to be consistent. This consistency in formulas across rows/columns is the correct approach to modelling in Excel.
Therefore, I wouldn't persist trying to make this formula work for a single cell, because you'll only need to change it for the next cell and so on.
Mynda
