Okay, so I will try to give as much detail without confusing anyone.
I am trying to make a 2023 attendance tracker for my team since they have not yet given us access to a new one. I have a legend with the "Codes" that will be used (FMLA, ATO, SATO, etc) and what value those should equal (0,1, or .5).
I then have a large area where each month is a column and the y-axis is the dates 1-31. At the bottom of each column is a total row that I have set up to calculate the SUM of the corresponding month's column. The issue I have is that I need to be able to fill in on the calendar portion the "codes" and have those codes equal the correct value, and then that value is shown in the totals row.
For example, cells A7:A37 contain the numbers 1-31 (Days of the month), cells B6:M6 contain the months of the year, and cells B38:M38 contain column totals. On Jan 15th I have an agent call out sick, this is code SCKU and should equal 1. Then, on Jan 25th the same agent is late by less than 2 hours, this is code Late<2 and should equal 0.5. To track this I would put code SCKU in cell B21 and Late<2 in cell B31 and cell B38 would show 1.5.
I've already gotten through the conditional formatting so that the cells follow the correct shading rule when you enter text, its the counting part that I just can't seem to figure out. I tried Name Manager and it doesn't work when using the formula =SUM(B7:B37) in cell B38.
Bookmarks