Need a creative mind for help

[JRT2006]

This may be a stretch, but I'll reach out to those with a bigger brain and mind than mine.

This excel sheet tracking overtime at my work is all manually input by someone in a higher position than me but with poor excel and management skills. The picture attached is how the tracking works, if difficult to understand I can explain. Overtime began with the highest ranking being offered first, if they declined an "X" was put in the cell corresponding to the employees and the next employee was asked until someone says yes. A "Yes" is put in the corresponding cell to that employees name and "Next" is put on the employees cell below indicating that is the next employee to be asked when the next overtime is available. At the top of the table you will see a 1, 2, 3, or 1 CC. That is the number of overtime employees needed. The CC means the employee must hold certain certifications to fill the overtime position.

I'm curious if there's a way to automate the process rather then manually inputting everything. Place a "Yes" on the person accepting overtime and "Next=>" automatically fills the next cell if only one overtime is needed, etc. If not, can anyone think of a more easily organized and less confusing way to track it?

We don't have access to the master document so we copy it to our desktops to update it ourselves as management fails to do so, or do so correctly.

Thanks

 

[dermie_72]

When you're relying on input from an outside source (The employee accepting or declining) how do you automate it? You still need to manually update the employees acceptance.
You could have a formula linked to the prior employee saying if they decline, this person now has the opportunity to accept or decline....IMHO

 

[JRT2006]

When you're relying on input from an outside source (The employee accepting or declining) how do you automate it? You still need to manually update the employees acceptance.
You could have a formula linked to the prior employee saying if they decline, this person now has the opportunity to accept or decline....IMHO

That's kind of what I was getting at, I probably just said too much and made it confusing.

When the boss puts Yes for an employee accepting overtime or an X for declining, the employee next on the list automatically shows as being next.

Just wasn't sure if there was a better idea or even a better way to organize the list.

 

[dermie_72]

Sorry, but the data doesn't make sense. Im assuming that Next means the overtime is full and to ask the next employees the next day, however Column H doesn't support this. For the certified overtime, is there a qualifier on the employee that denotes if they're capable to doing that shift?

 

[JRT2006]

Sorry, but the data doesn't make sense. Im assuming that Next means the overtime is full and to ask the next employees the next day, however Column H doesn't support this. For the certified overtime, is there a qualifier on the employee that denotes if they're capable to doing that shift?

Column G there was 1 overtime available. Mr. G, Mr. H, Mr. V did not take it Mr. T did. Therefore the next time overtime is available they will start with Mr. Z.

Column H there are 3 overtimes available. Mr Z said no, Mr. I said yes (2 OT slots available still), Mr. J said no, Mr. S said yes (1 OT slot available still), Mr. M said no (goes back to the top of the list), Mr. G and Mr. H said no, Mr. V says yes (all overtime positions are full), next available overtime will begin with Mr. T.

Sometimes there are days where the position needed for overtime requires an employee with certain certifications. Therefore they will only ask those employees with that certification. I tried to show this at the end of the chart (column P). If they follow the list and have to skip an employee that doesn't have the required certs, the next overtime will begin at the skipped employee. Confusing I know