Overtime sheet in Excel

Originally Posted by nolomcdc

Currently I have a list of employees in the Employees tab that will populate in order when the respective number is placed in C2 in the Employees tab.

what

which list is which

how is this supposed to work

I would like for the Staff Rotation in OT Sheet tab to auto fill so that if I pick Staff D in B31 Staff E will auto populate in B32 and so on.

what do you mean by "and so on"

Also, I dunno what you're trying to do, but doing it with merged cells is wrong.

In D2 and pull down:

Please Login or Register  to view this content.

In E2 and pull down:

Please Login or Register  to view this content.

No dropdown needed, as long as you have less than 999 employees. (In which case you just, like, change the number to be bigger).

That do it for you?

This proposal will employ a number of named ranges based on cells P30:Y39 (which may be moved and/or hidden for aesthetic purposes).
P30:Y30 is named Rotation_1
P31:P39 is named Staff_A, Q31:Q39 is named Staff_B etc.
B31 is populated using a drop down that uses Rotation_1 as the source.
B32:B40 are populated using: =INDEX(INDIRECT(SUBSTITUTE(B$31," ","_")),ROWS(A$1:A1))
Let us know if you have any questions.

Try to upload again and if it doesn't work this time I'll upload the file again, although I just opened it from another computer without issue. The formula possibly yields an #REF because the named ranges have not been set up.

Here is the file again.
Perhaps if there is another computer from which you could download that might work.

Perhaps this will be better.
On the employee sheet A1:B11 is converted to tbl_Staff which may be appended by selecting cell B11 and pressing the Tab key.
On the OT sheet cell M31 is populated using: =INDEX(tbl_Staff['#],MATCH(B31,tbl_Staff[STAFF ROTATION],0))
M32:M40 are populated using: =IF(MOD(SUM(M31,1),Employees!F$1)=0,Employees!F$1,MOD(SUM(M31,1),Employees!F$1))
Note that M31:M40 may be moved and/or hidden for aesthetic purposes.
Cell B31 is still populated using data validation with the source =Rotation_1.
The 'Refers to:' of the named range Rotation_1 is changed to =tbl_Staff[STAFF ROTATION]
Cells B32:B40 are populated using: =INDEX(tbl_Staff[STAFF ROTATION],MATCH(M32,tbl_Staff['#],0))
Let us know if you have any questions.