Open Office: Rotate name in column list to bottom when used in another column

[greg9719]

Hello all,

I've been progressively increasing my excel skills and looking for some help with utilizing excel to manage a rotating overtime list of our staff.
First off I have Office 2019 (at least on my company laptop, not sure what is on my office computer or home laptop).

In the attached sheet, if I enter an employee name into column C I'd like their name in column F or G to move to the bottom of the list in that column. For example, I enter "employee 7" on the first open shift of 1-Jan. Their name in column F would go to cell F23 and employee 8 through employee 21 would all shift up.
Same for the PRN column.

I am about as basic/novice as they come and do not understand VBA's, X lookups, and struggle with applying functions to areas. As basic an explanation as can be offered would be appreciated.

[dflak]

I just noticed that you have open office instead of "real Excel" so this might not work.

First of all, I converted the ranges into tables. Tables have too many good things going for them not to use them. One of them is that tables "know" how big they are. Another is that tables copy down formulas, formats and validations. So when you add a date to cell A4, the entire row of the table comes into existence with the formulas and validations already in place.

The orange tables are used for data validation and for shuffling the contents.

I have a cell change event on columns 3 and 4 (C & D) which kicks off the calculation if needed.

The logic is simple. The program finds the highest Count in either the FT Staff Table or PRN table depending on which column is being updated.

Then it finds the row that has the selected value and assigns one more than the existing maximum to the count column associated with the selected value. Then it sorts the table based on this value.

The next time the data validation comes up it comes up based on the new order.

[greg9719]

That worked perfectly and as I had hoped. Thank you so much. I will start doing my homework on tables, data validation, and shuffling contents.

[greg9719]

I actually do have another question. It may be the version I am using but if i select an employee in the drop down in the assignment column it rotates appropriately. Yet if I elect to delete that person for instance if I wanted to change the person it leaves them at the bottom of the list. I get a debug error when I delete any of the values within the table consisting of the opening date, base, assinment (Columns A, B, C, D)