IF FUNCTION WITH DATA VALIDATION (ie, if C3 has data, F3 must be completed)

[thedeadzeds]

Hi guys,

I've got a basic spreadsheet (attached) with data validation and depending on what is input into a certain cell, I wanted a few different things to happen:

1. Column F from F3 to F300 must have some data input if column C has a date in it. ie, IF C3 has a date then F3 must be completed and so on C4 THEN F4 must be completed etc.
2. IF C3 has a date, either H3, I3 or J3 must be completed (but only one out of the 3) and so on down the list
3. IF H3, I3 or J3 is completed then K3 must be completed and so on down the list
4. IF K3 is completed, L3 automatically shows 'Yes'. If not, show No and so on down the list.

Hope this can be done,

Thanks in advance
Craig.

 

[Ken Puls]

Okay, for the first 3, I'd use conditional formatting. You'll want to:

• Select F3:F302 and create a new conditional formatting rule based on a formula
• Formula: =AND($C3>0,LEN($F3)=0)
• Set the condition you want by clicking on Format (maybe a red background?)

• Select H3:J302 and create a new conditional formatting rule based on a formula
• Formula: =AND($C3>0,COUNTA($H3:$J3)<>1)
• Set the condition you want by clicking on Format (maybe a red background?)

• Select K3:K302 and create a new conditional formatting rule based on a formula
• Formula: =AND(COUNTA($H3:$J3)=1,LEN($K3)=0)
• Set the condition you want by clicking on Format (maybe a red background?)

For the last one, just use the formula (in cell L3) and copy it down: =IF(LEN(K3)>0,"Yes","No")

 

[thedeadzeds]

Thanks very much, works like a treat