HELP PLEASE! Looking to create a "Mandatory Overtime" list that rotates after the employee is held for a shift.

[DevinB]

Hello. I am new to the forum. I have spent about a week looking for answers and trying to solve this issue on my own. I am fairly well-versed with Excel... more so than a "general user"; however, I have little experience with coding and VBA.

I have attached a mini-sheet of my list; it's pretty basic.

There are only 2 columns that I am concerned with that will need some type of conditional sorting and/or formatting... the Employee and Date Held columns. The third column is irrelevant since the employee will rotate to the bottom of the list whether they are held on overtime or volunteer for overtime.

As you can see the Employee list is in alphabetical order. This is our starting point for this new way of handling mandatory overtime.

When an employee on the list is held for overtime, the date they are held is entered into the column next to their name. When that date is entered, I need them to rotate to the bottom of the list. Thus having the next alphabetical (A-Z) employee that has not been held (no date beside their name) at the top of the list.

Sure, I can use the "sort" feature in the date column, but I want the employees that have a date beside their name grouped at the bottom of the list in an order that reflects the oldest held (by date) at the top of that group.

Once every person has been held or volunteered to work and subsequently has a date beside their name, I can just sort the Date Held column. But I have users that may not know (or remember) to manually sort the list before they print and post the list. I'd like that to be automatic.

If anyone can help, I'd be greatly appreciative. I know this may be complex, but I'm willing to work with whoever can help.

Sample Overtime List.xlsx
	A	B	C	D	E	F
1	Mandatory Overtime List
2	ALS			BLS
3	Employee	Date Held	Held or Voluntary	Employee	Date Held	Held or Voluntary
4	Bob, Joe			Bob, Joe
5	Cat, Bob			Cat, Bob
6	Dog, Hot			Dog, Hot
7	Edward, John			Edward, John
8	Flint, Bill			Flint, Bill
9	Hill, Jim			Hill, Jim
10	Jameson, Jon			Jameson, Jon
11	King, Tom			King, Tom
12	Moon, Leah			Moon, Leah
13	North, Jill			North, Jill
14	Oscar, Jean			Oscar, Jean
15	Papa, Big			Papa, Big
16	Red, John			Red, John
17	Smith, Tracy			Smith, Tracy
18	White, Mister			White, Mister
MOT List

 

[Micron]

Perhaps enter a bogus date in the past to start with for each person (e.g. 01/01/2000) and sort by date Oldest to Newest and then by employee A to Z? Then alter the date for employee when they work OT. If code isn't used then I guess you'd have to apply the sort after each entry. Not sure if you'd need to split left and right groups onto separate sheets or not.

 

[DevinB]

Micron:

Thanks for that information! That does help add to my thought process.

Do you know of a way to add a "button" or some other way to sort automatically on "update" after an entry is made and the enter key is pressed?

 

[Micron]

You could use the macro recorder and go through the sort process to create some code. Then you can assign a keyboard combination to run it. If you prefer, after creating the code you could cut and paste it into a button click event (don't copy the Sub and End Sub parts though). The macro recorder can teach you a few things about coding if you're not adept at vba.

 

[DevinB]

You could use the macro recorder and go through the sort process to create some code. Then you can assign a keyboard combination to run it. If you prefer, after creating the code you could cut and paste it into a button click event (don't copy the Sub and End Sub parts though). The macro recorder can teach you a few things about coding if you're not adept at vba.

Ok, great. Thank you for all the information! I will reach out if I have any other questions.