Skrej
Board Regular
- Joined
- May 31, 2013
- Messages
- 176
- Office Version
- 365
- Platform
- Windows
I have an attendance sheet on a shared drive we use for classroom attendance. We've had this same sheet for several years now, and it works as intended.
Leftmost column has student names, with subsequent columns to the right marked with attendance codes of 'P', 'A', or 'E'. On top of each of those columns is a date. The rightmost column tallies all the 'p' count with the simple formula (copied down) of "
This has been working just fine for a few years. However, yesterday I encountered a strange issue. I noticed that the tally count was one less than the number of 'p'. The tally count was consistently one off for every row.
While trying to troubleshoot, I noticed that one column (happened to be Mar. 7th, but I don't think that's relevant) wouldn't let me change the attendance code. I was working through adding extra 'p's to watch the tally count, and noticed that for that one particular column, when I typed a lowercase p over the uppercase P, it just kind of blinked and stayed uppercase. The forumula isn't case sensitive, and the only reason I was overwriting with lowercase 'p' was to keep track of my troubleshooting.
Sure enough, that entire column wouldn't update. I tested by trying to add lowercase p's below the last row with an actual student name - same deal. I typed lowercase, it automatically updated to uppercase, but tally count wouldn't update. So, I surmised that the tally problem wasn't reading the uppercase P in that column only. No idea why.
Here's where it gets even weirder. I accidentally discovered that if I continued adding lowercase 'p' the next row under, working DOWN, it did the auto-uppercase thing with no tally count. (At this point, I'm dealing with otherwise blank rows). However, if I skipped down several rows, and started working m;y way back UP the column, it would not only accept the lowercase 'p', but would then add one to the tally column as it should. If I continued working my way UP the column, it would then let me overwrite with lowercase, and the tally count updated!
I then tested a bit more, and replaced the lowercase P's in that column with uppercase, and things were back to normal. I could use uppper or lowercase letters as I wanted, and the tally would update. I tested a bit by changing some of the 'p' codes to 'A' or 'E', and back to uppercase P, and the tallies subsequently updated as expected. Saved it, closed it, re-opened it, and it was still working normally.
We have multiple copies of that sheet, one for each month within the workbook. I checked previous and future months, and the error did not appear on other sheets. We have multiple copies of that workbook, each identical except for the student and teacher names. Each instructor has three copies of the workbook, one for each individual class - all identical, as they're simply made from one master, then renamed with unique class name. The only difference is the student and teacher names, and of course individual student attendance codes.
I checked all other versions of that workbook for all other classes (all are stored on a shared drive), and found no other instances of the odd issue in March or any other month. It was unique to that one teacher's single class, on that one single month on that one single column. That suggests that it wasn't something in the layout or formulas, or else it would have been duplicated with all the copies from the original master.
The sheet is locked and protected so that individual instructors can't do anything except add student names and mark attendance codes (P/A/E), which are the only unlocked cells. I have it set so that they can't even select the locked cells, and it's password protected. I don't see how the instructor could have made any substantial changes to result in this bug.
Any ideas on what happened? It was a fluke we happened to notice this instance, so I'm wondering if it could happen again. I just don't understand why I couldn't update the cell if I worked vertically downward, but skipping down a few blank rows and working up the same column would then magically work.
Leftmost column has student names, with subsequent columns to the right marked with attendance codes of 'P', 'A', or 'E'. On top of each of those columns is a date. The rightmost column tallies all the 'p' count with the simple formula (copied down) of "
Excel Formula:
=COUNTIF(D7:V7, "P")*2This has been working just fine for a few years. However, yesterday I encountered a strange issue. I noticed that the tally count was one less than the number of 'p'. The tally count was consistently one off for every row.
While trying to troubleshoot, I noticed that one column (happened to be Mar. 7th, but I don't think that's relevant) wouldn't let me change the attendance code. I was working through adding extra 'p's to watch the tally count, and noticed that for that one particular column, when I typed a lowercase p over the uppercase P, it just kind of blinked and stayed uppercase. The forumula isn't case sensitive, and the only reason I was overwriting with lowercase 'p' was to keep track of my troubleshooting.
Sure enough, that entire column wouldn't update. I tested by trying to add lowercase p's below the last row with an actual student name - same deal. I typed lowercase, it automatically updated to uppercase, but tally count wouldn't update. So, I surmised that the tally problem wasn't reading the uppercase P in that column only. No idea why.
Here's where it gets even weirder. I accidentally discovered that if I continued adding lowercase 'p' the next row under, working DOWN, it did the auto-uppercase thing with no tally count. (At this point, I'm dealing with otherwise blank rows). However, if I skipped down several rows, and started working m;y way back UP the column, it would not only accept the lowercase 'p', but would then add one to the tally column as it should. If I continued working my way UP the column, it would then let me overwrite with lowercase, and the tally count updated!
I then tested a bit more, and replaced the lowercase P's in that column with uppercase, and things were back to normal. I could use uppper or lowercase letters as I wanted, and the tally would update. I tested a bit by changing some of the 'p' codes to 'A' or 'E', and back to uppercase P, and the tallies subsequently updated as expected. Saved it, closed it, re-opened it, and it was still working normally.
We have multiple copies of that sheet, one for each month within the workbook. I checked previous and future months, and the error did not appear on other sheets. We have multiple copies of that workbook, each identical except for the student and teacher names. Each instructor has three copies of the workbook, one for each individual class - all identical, as they're simply made from one master, then renamed with unique class name. The only difference is the student and teacher names, and of course individual student attendance codes.
I checked all other versions of that workbook for all other classes (all are stored on a shared drive), and found no other instances of the odd issue in March or any other month. It was unique to that one teacher's single class, on that one single month on that one single column. That suggests that it wasn't something in the layout or formulas, or else it would have been duplicated with all the copies from the original master.
The sheet is locked and protected so that individual instructors can't do anything except add student names and mark attendance codes (P/A/E), which are the only unlocked cells. I have it set so that they can't even select the locked cells, and it's password protected. I don't see how the instructor could have made any substantial changes to result in this bug.
Any ideas on what happened? It was a fluke we happened to notice this instance, so I'm wondering if it could happen again. I just don't understand why I couldn't update the cell if I worked vertically downward, but skipping down a few blank rows and working up the same column would then magically work.
