Force a user to enter text when text is entered into another cell

Hi all,

Example:

If we had a list of students in cell A1 - A20 and the word 'absent' was written into cell B1 - B20 next to the student, how do i make a command/prompt that forces the user to enter the 'reason/text' into cell C1 - C20?

Thanks

Force the user? Can't in terms of moving to the corresponding col C cell and not leaving it until the user enters something. Note that cleverer users would add a single space character, or if you try to be clever and TRIM its contents, just a period. Point: real data validation is rather difficult.

If you don't want to use VBA, you could use a formula in some blank portion of the screen. Like this (assuming row 24 otherwise blank),

A24: =IF(SUMPRODUCT((TRIM(B1:B20)="absent")*(LEN(TRIM(C1:C20))<4)),"ABSENCE IN B"&MATCH(1,INDEX((TRIM(B1:B20)="absent")*(LEN(TRIM(C1:C20))<4),0),0)&" without REASON given in column C","")

Have that centered across several cells and format A24 to show red, boldface text. Whenever a cell in B1:B20 shows absent and the corresponding col C cell contains fewer than 4 characters (modify as you see fit), this warning message will appear. It won't make your users fill in col C, but it'd tell them they need to do so.