Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a spreadsheet with names in column A. I'm keeping attendance by placing an "X" in each subsequent column when the person is present (week by week).

I'd like for Excel to automatically change the range of cells to RED when the cell plus it's preceding two cells are blank, effectively showing red when someone has been absent three weeks in a row.

How can I accomplish this using Excel 2010?

share|improve this question
2  
Sounds like you need conditional formatting using a formula to determine when the RED flag should be flying. Access CF from Home tab of the ribbon. –  chuff Jun 5 '13 at 17:06

2 Answers 2

As chuff says in their comment you can do this with conditional formatting. Actually building the necessary formula is moderately tricky, since Excel does not make it easy to refer to the cell to be formatted to be referred to directly. Here's a solution that works around this difficulty, though I'm sure it's far from the most elegant one possible:

=3=COUNTBLANK(INDIRECT(CHAR(COLUMN()+62)&ROW()&":"&CHAR(COLUMN()+64)&ROW()))

The idea here is to build a string containing the range of interest (the cell to be formatted and its two neighbors to the left) using ROW() and COLUMN(), which INDIRECT() then makes into a reference to that range. The function of COUNTBLANK() is hopefully obvious.

EDITS: As noted in the comments below, there's some ambiguity in your request. The above formula should work if what you want to start highlighting with the third absence. That is, in a run of exactly 3 absences, only the third will be highlighted. Perhaps instead what you want is to highlight the entirety of any run of absences of length 3 or greater.

It's even less pretty, but my solution can certainly be extended to do that. Above we check if a cell is the last in a run of three. We can also check if it's in the middle or the beginning of such a run, then OR the whole thing together:

=OR(  
    3=COUNTBLANK(INDIRECT(CHAR(COLUMN()+62)&ROW()&":"&CHAR(COLUMN()+64)&ROW())),
    3=COUNTBLANK(INDIRECT(CHAR(COLUMN()+63)&ROW()&":"&CHAR(COLUMN()+65)&ROW())),
    3=COUNTBLANK(INDIRECT(CHAR(COLUMN()+64)&ROW()&":"&CHAR(COLUMN()+66)&ROW())),
   )
share|improve this answer
    
It will highlight any cell for which that cell and its preceding two neighbor cells are blank, which is what I intended it to do. But re-reading the question I can see how this is perhaps not the most natural interpretation of the desired behavior. –  Curtis H. Jun 6 '13 at 16:09
    
Could you upload a screenshot of a test case where it fails? I'm either misunderstanding you or unable to duplicate the failure case. –  Curtis H. Jun 6 '13 at 16:19
1  
Here's what I get for that table: i.imgur.com/yeNcwYz.png Are you getting something different? This looks right to me, assuming we only want to start coloring from the third consecutive absence. –  Curtis H. Jun 6 '13 at 16:45
    
What a relief! Our difference is merely in interpretation of the Q. I was expecting G2:H2 and J4:K4 (in your image) to be highlighted also (But there is no need - and I am not adamant that my 'interpretation' is correct!) –  pnuts Jun 6 '13 at 16:52
    
Yes, I was hoping that's all it was. I don't think it's obvious what the asker actually wants, so I've added an extension of my original answer which I think should be in line with your interpretation. –  Curtis H. Jun 6 '13 at 17:15

I couldn't get COUNTBLANK or IF(OR({Cell}="x")) type checks to work correctly for me, so I just did this:

  1. Starting in the first student's third column of attendance dates
  2. Click "Conditional Formatting" -> "New Rule..."
  3. Choose "Use a formula to determine which cells to format"
  4. Enter (assuming attendees start on row 1 and dates start in column B) =COUNTBLANK(B1:D1)>2 in the "Format values where this formula is true" box
  5. Click "Format..."
  6. Go to the "Fill" tab, choose red, click OK
  7. Click OK.

Then you just have to click the crosshair at the lower right corner of the cell and copy formatting across the row. Click the crosshair again and copy formatting to row(s) below, then repeat for those rows (for some reason, I couldn't copy formatting for this condition to a mass range of cells)

share|improve this answer
    
the 'erratic' may be from me not taking into account that the field should only be red if a third consecutive week is missed. Above it only checks two prior weeks. The formula should be: '=COUNTBLANK(B1:D1)>2' I'll edit the answer, but leave the original so your comment makes sense. –  panhandel Jun 6 '13 at 15:26
    
(I have deleted my comment.) D1)>2 seems better than C1)>1 but in my sample sometimes highlights 3 in a row (good! but only when the latest of more than 3?) but sometimes only the third (of 3). Great shame as this is very close and very simple (relative to a variation of Curtis's answer, that I think would work but be horribly long as a single formula). –  pnuts Jun 6 '13 at 15:43
    
I think I get what you're saying about consecutive red fields, and possibly only marking the very last missed date red(correct me if that's not what you mean), but I didn't gather that as a requirement by the OP. I thought any individual date should be red if it and the previous two dates were all three missed. So if six consecutive dates between 'X's were missed, the first two date columns would be white/normal, but the next four would be red. –  panhandel Jun 6 '13 at 16:22
    
The problem is with trying to 'look back'. I think your formula does not highlight the first two 'blanks' in a series of only three 'blanks'. –  pnuts Jun 12 '13 at 12:31
1  
I didn't think the first two needed to be highlighted, but I can see from the way the question currently reads how that can be interpreted. Conditional formatting settings for one field cannot conditional format other fields near it though, so if that's required, a more robust method is going to be needed. –  panhandel Jun 12 '13 at 14:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.