I am a school teacher and pupils get warnings on a spreadsheet in dated
columns. The dates are in black, and then using conditional formatting, grey
out if older than 2 weeks.

In the dated columns, a warning can be displayed as C1, C2, C3 or C4. The
pupils name is in column B and class in column C. Warnings are in columns D
onwards.
What I would like is, if a pupil receives three different warnings, within
the recent dates (not greyed out), then their name goes onto the next line in
a new sheet, for example called "Phone Calls".