Auto populate cell based on raw data

Question

HI,

I am trying to create a roster that automatically shows the employees name as per the raw data. so for dates I've used the today() function and as the date automatically changes the cell next to it should automatically show the data from the RAW Data. Group A , Employees have fixed shift as per day (Saturday and Sunday no one is working hence its an off). However, in Group B there are 2 Employees and there working day will come alternatively as the date changes.

So what formula will appear under group A and Group B in order to make it appear as the final picture mentioned in reply.

Answer 1

Hi Berly George,

Try these formulas:

E3= INDEX($J$4:$J$10,WEEKDAY(D3,2))

Copy E3 to E4, and so on.

F3 = INDEX($K$4:$K$10,WEEKDAY(D3,2))

Copy F3 to F4, and so on.

If you have any questions, please feel free to reply.

Kind regards, Pondsi

Answer 2

Hi nt1809,

Group A, I was able to fix with the help of your formula however Group B is formula didn't work.

Answer 3

Hi Berly,

I've tried to recreate your sheet and come up with a formula for each group, hopefully they work but I'm not entirely sure as I don't have access to your sheet, I just recreated it to the best of my ability.

For Group A (ie, you would put this in cell E3 and then drag it down), try: =IF(OR(TEXT(D3, "ddd")="Sat", TEXT(D3, "ddd")="Sun"), "Off", VLOOKUP(TEXT(D3, "ddd"), I4:J10, 2, FALSE))

For Group B: =IF(TEXT(D3, "ddd")="Sat", INDEX(I10:J11, 1, 2), IF(TEXT(D3, "ddd")="Sun", INDEX(I10:J11, 2, 2), INDEX(I10:J11, MOD(ROUNDUP((D3-$D$3)/2,0),2)+1, 2)))

I hope these solutions work, but if not, feel free to let me know and I'll have another go. It might also be easier if you can share a version of the document with me, but just be careful that your data is anonymized if that matters in this context.

Kind regards, nt1809

Answer 4

image upload was giving me error hence uploaded in reply

Roster

formula

Final