Rolling 365 day tracking sheet

And where do you expect the result (which cell) and what is the result e.g. for person no. 1

Since it is a huge file, it is difficult to handle.
Why is NV 4 (since the result in that cell is 3).
It uses the sum formula, so that looks ok to me.

you want a rolling absence, I think you better of using a table (data in rows instead of columns).

to give you an idea.

a macro to rearange the data.

after that a pivot table.

see the attached file.

if you want a rolling 365 tracking sheet you need to add 365 columns with a formula.

It the file more hugh than it is on the moment.

How would you analyse the data that way.

Put this formula under a column called Absent (corrected code below if you already tried previous code)

Please Login or Register  to view this content.

Change ($K$5:$NK$5) to the range of your dates. If your dates start in Row 4 column D and end in row 4 column Z then it would be ($D$4:$Z$4
Change the ($K6:$NK6) to the range of your first employee. Then copy/paste the formula straight down for it to change to K7, K8, K9 etc.
Change CO to whatever you enter in the cell for an absent. Or you can set up a drop down menu via data validation.

Then add columns for every date. You can add a date 8/15/2017, drag corner of cell to the right to populate the rest of the days in the same row. Click the little box after dragging the corner to select weekdays only if you need.