Need help with a lateness tracker.

Hello,

this whole thing feels wrong from the start.

Why the blank rows between the agent names in the first sheet? Interspersing data with blank rows will not do any good. Ever. Quite the opposite, in fact.

Instead of having four sheets, one for each quarter, with each date pre-populated in a column, I suggest that you build a completely different design:

One sheet for data entry, with fields for Date, Agent Name, and whatever value(s) you want to track. Is it time checked in? Minutes late? That's not quite clear.

Every agent's data is entered on that sheet, for the whole year. No blank rows. No gaps. Only add a row when there is data, i.e. if you track "minutes late" and Jones is on time all January, there will not be an entry for Jones.

From that one data table you can then create reports, for example pivot tables where you can quickly see values by month or quarter and by agent.

If you have the data architecture down correctly, you will save heaps of time down the road for reporting and when things change.

cheers, teylyn

... and that can be done with a report like a dynamic pivot table, where you can select the quarter to report on. It does not mean you have to start with bad data architecture.

I'm trying to help you, not work against you.

What data is actually entered? Minutes late? Start time? How is the data aggregated? Sum of something? Average of something?

Exactly what I had in mind.

Consider this:

Data entry is here:

datasheet.png

The pivot table is on another sheet and has slicers for the Year and the quarter. In your old solution, your boss would click a sheet tab. In this solution they would click a slicer. Same effort.

Q4Report.png

To see the different quarters, just click on the quarter slicer. The pivot table can be configured to show more detail (say, the days that people were late) or less detail (say, just the number for the quarter, not for each month).

It's quick and easy to see which employee needs to submit a slip for personal time.

As an added bonus, you can see the data on a yearly basis if you remove the slicer filters. Or you could quickly create a chart where you can see trends about when people are late (always on a Monday morning?).

With a simple data entry table you can easily do all kinds of things, not just the one thing.

I'm attaching the file.

cheers, teylyn