Finding consecutive(-ish) blank cells

[narnian_uk]

I'm working on an attendance spreadsheet for a 12-week term as shown in the attached image. Attendable events may take place in consecutive weeks (eg 1, 2, 3, 4, 5, 6; 'model A') or in some weeks only (eg 1, 3, 5, 6; 'model B'). The spreadsheet contains a dash ('-') in a grey cell in weeks during which no event will take place. Attendance is marked by manual entry of some other character (eg 'P' for 'present'), while non-attendance is indicated by the cell being left blank. The current term week is highlighted in yellow.

I'm trying to work out how to highlight the names of students who haven't attended

• either the two consecutive sessions prior to the current week if the current week's session hasn't taken place (regardless of whether those sessions are consecutive in the model A sense or consecutive in the model B sense), or
• if the current week's session has taken place (indicated by the presence of 'P's or similar in the column for that week), the current week's session and the preceding one (regardless of whether 'preceding' is in the model A or model B sense).

In the attached image, the first student's name would be highlighted because it is currently week 4 and they have missed teaching events in weeks 1 and 3 (there being no event in week 2, and the week 4 session not yet having taken place).

I have to admit to his being rather beyond my abilities with Excel; I'm familiar with conditional formatting etc., but I'm not at all sure how one could identify the cases above without resorting to VBA (which isn't an option here) - so any pointers at all would be very gratefully received (including 'can't be done; stop wasting your time')!

 

[Eric W]

I found this quite challenging, so I understand your frustration. Here's my stab at it:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Attendance Sheet Title		Most recent COMPLETED event	3	Starting column	1
2
3	Surname	First Name	Preferred Name	1	2	3	4	5	6	7	8	9	10	11	12
4	Surname A	First Name A			-				-								1
5	Surname B	First Name B		P	-				-								0
6	Surname C	First Name C		P	-	P	P		-								0
7	Surname D	First Name D		P	-	P	P	P	-								0
8	Surname E	First Name E			-	P			-	P							0
9	Surname F	First Name F		P	-	P			-								0
Sheet1

Cell Formulas
Range	Formula
F1	F1	=LET(week,$D$1,dashes,COUNTIF(OFFSET($D$4,0,SEQUENCE(,week,0),1,SEQUENCE(,week,week,-1)),"<>-"),XMATCH(2,dashes,0,-1))
Q4:Q9	Q4	=IF(COUNTIF(INDEX($D4:$O4,$F$1):INDEX($D4:$O4,$D$1),"P")=0,1,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A4:C9	Expression	=$Q4	text	NO

First, I didn't see how you identified what week you were looking at. There was some highlighting on your sample sheet, but that's not something I could work with, so I added the D1 cell which says which week you want. That will identify which is the last week I need to check. Then the complicated formula in F1 figures out which is the starting week I need to check, based on where the dashes are. Then the helper formulas in Q4:Q9 say if they find any Ps in that range or not. Finally, the Conditional Formatting just looks at those helper formulas to decide whether to highlight the names or not. The helper formulas cannot be put into CF, since CF does not like the variable ranges. You can just hide those cells if you want.

Hope this helps!

 

[narnian_uk]

I found this quite challenging, so I understand your frustration. Here's my stab at it:

It really does help. This is brilliant - thank you so, so much. I've tweaked one part (primarily the cell that contains the current week number - it counts the number of weeks since a given date and limits the maximum value to 12; if there are any non-blank cells in the column for the current week, then it returns the current week number on the basis that an event has already taken place this week; if not, then it returns the previous week number), but this is going to work very nicely. It's also introduced me to various bits of Excel I wasn't familiar with - LET and SEQUENCE in particular - which I'm now using elsewhere on the spreadsheet.

 

[Eric W]

LET, SEQUENCE, and XMATCH are all relatively new, so it's not surprising you hadn't seen them yet. But they are powerful in the right situations.

Anyway, you took my ideas and ran with them! Glad I could help.