Point of work-wise (POW) Allocation in rotation

Hi Excel Experts,
I am working Point of work-wise (POW) Allocation in rotation manually,

Now i am looking for some automated task for the same..

- Available data (In a Worksheet) :
We have allocation in terms of D, N, G etc. and OFF,
- Requirement data / Result (in another Worksheet) :
We have to allocate all the staff in following manner in rotation;
21 for D (Day Shift) : D0, D1, D2, D3, D4, D5, D6, D7, D9, D10, D11, D12, DS, DY1, DY2, DP, DR1, DR2, DR3, DR4, DR5.
And
21 for N (Night Shift) : N0, N1, N2, N3, N4, N5, N6, N7, N9, N10, N11, N12, NS, NY1, NY2, NP, NR1, NR2, NR3, NR4, NR5
Criteria
- All D (Day Shift) staff are to allocated in 21 different Point of work(POW) and same for N(Night Shift)
- Suppose, D0 allocated for staff A, in next duty (when he was in D or N; excluding OFF, G, DT etc.), he should be in D1 or N1 and like-wise.. 2, 3, 4, 5, 6, 7, 9, 10, 11, 12, S, Y1, Y2, P, R1, R2, R3, R4, R5.
- Apart from 2 fix allocated persons (DP or NP) all other staffs are to be allocated into 20 POW
- In case of less than 20 let it should be blank in last POW.
(in attached file i have don for 5 Jun, i want the same in all the days(D & N) in automatic manner; Allocation of all the 21 staff in above manner and IN ROTATION to avoid fix allocation;
Kindly help me to solve the above task.
- Ravi

I have prepared allocation sheet, however, it's not working when we select date & shift in some shifts

Further
1201 Pramod Rohidas Patil & 1212 Mangesh Govardhan Gharat are fixed for Pre-gate and other all the staff in following manner in rotation;
ODC Gate
Lane_01
Lane_02
Lane_03
Lane_04
Lane_05
Lane_06
Lane_07
Lane_09
Lane_10
Lane_11
Lane_12
South Gate
Yard Gate_01
Yard Gate_02
Pre-Gate (fixed POW for 1201 Pramod Rohidas Patil & 1212 Mangesh Govardhan Gharat )
Reliver_01
Reliver_02
Reliver_03
Reliver_04
Reliver_05

Kindly help me in this regards.

- Ravi

Dear Excel Experts,
Kindly help me to solve the above task.
- Ravi

Sir,
Thanks for your prompt response and valuable information,
I have updated formula as per your instruction (file is attached for ref.), however, result is not appearing as desired..

requirement
- We have to allocate all the staff in following manner in rotation (whenever staff deployed in D and N);
ODC Gate
Lane_01
Lane_02
Lane_03
Lane_04
Lane_05
Lane_06
Lane_07
Lane_09
Lane_10
Lane_11
Lane_12
South Gate
Yard Gate_01
Yard Gate_02
Reliver_01
Reliver_02
Reliver_03
Reliver_04
Reliver_05

and

Pre-Gate (fixed POW for 1201 Pramod Rohidas Patil & 1212 Mangesh Govardhan Gharat )

Thanks in advance

- Ravi

Sir,
I tried as per your suggestions but,it's not working,

- Ravi

Sir,
Rotation is not taking place
for example for 6 Jun
1391 Ajit Thakur is at ODC Gate then on 7th Jun D he should move /be in immediate next duty at next POW i.e.Lane_01
then Lane_02
then Lane_03
then Lane_04
then Lane_05
then Lane_06
then Lane_07
then Lane_09
then Lane_10
then Lane_11
then Lane_12
then South Gate
then Yard Gate_01
then Yard Gate_02
then Reliver_01
like wise for all

- Ravi

Sir,
Can it be modified said file as follows

I have inserted
4 more rows (highlighted in yellow colour) above (in between fix and rotational staff)
1 more column (highlighted in yellow colour) (Gate (B) & 5th Jun(D))
Range is modified till XFA instead of CW, so that i will add further roster like-wise

Requested changes as follows;
1. In case of Revilers (Reliver_01, Reliver_02, Reliver_03, Reliver_04, Reliver_05), it is considered to be a single POW and then move to next POW
for On 6th Jun D 1391 Ajit Thakur is a Reliver_04 now on next immediate shift shift he should be moved to next; other than Revilers POW
(as per this file he is at Reliver_04 on 6th Jun D and then on his next shift he appearing again as Reliver (Reliver_01) on 7th Jun D

2. In case there is no person at POW Pre-Gate anyone from Relivers is to be appeared at POW Pre-Gate.

Thanks in advance.

- Ravi

Dear Sir,
Existing subject "Point of work-wise (POW) Allocation in rotation" can be continued in new thread or it should be changed., kindly advise

- Ravi