Auto Date and Value Rotation

[arjunagarwal07]

Hi Everyone,

I am running a small reading club for people in my area where the book has 48 chapters and 24 members and each member is given a roll No which is fixed.

I have created a small excel sheet which has all the chapters mentioned according to the Roll No's. Everyday I do prepare the sheet manually due to which sometimes error can happen and it becomes tough if they are noticed by the moderators in the evening when the group has to be closed.

I am looking for a way in excel to put the date automatically of one day ahead and the chapters list to be rotated on its own without doing copy and paste sort of work.

I have attached a file for sample.

In the file Roll No 1 will be reading Chapter 1&2 tomorrow, so the excel file should automatically create the next date of 24th May, 2021 and Roll No 1 to read chapters 3,4.

I hope this makes sense

[Gregb11]

Well, I'm not sure if I follow completely, but here is a simplistic solution.

In row 2, I first separated out the word "Date" and made the date and actual date in Excel. I selected columns A and B and copied them to columns D and E. For the date in row 2, I just put in the formula:
=B2+1

In cell E4 I entered:
=B5
and then copied it down.
in the last cell (E27) i entered:
=B4

For the next day, you can just copy those 2 columns (D & E) over to Columns G & H and the date and the chapters will change accordingly.

[josephteh]

Cell C2=TODAY()
Cell C4=INDEX($B:$B,IF((A4+$C$2-$B$2)>MAX(A:A),(A4+$C$2-$B$2)-MAX(A:A),(A4+$C$2-$B$2))+ROW($A$3))
Auto date and value rotation in column C