Fill other cells based on a condition in a cell

[srinidhi]

Hi All,

Cells for working are c,d,e,f.
If there is YES in any cell{c,d,e,f}, then other cells should automatically display as NO
I Tried the if or condition, it worked fines for cell c, when the formula is entered in d, it says circular error.
The formula in c3 =IF(OR(D3="Yes",E3="Yes",F3="Yes"),"No","Yes"). I cant enter the formula in d3 as =IF(OR(c3="Yes",E3="Yes",F3="Yes"),"No","Yes"). It is a circular error.

Please let me know the formula or a workaround. If it is not possible in formula.Request you to let me know the vba code.

 

[Hui]

Srindhi

You can use a CF rule like: =AND(COUNTIF($C1:$F1,"Yes"),NOT(C1="Yes"))

Problem is displaying NO

You can apply a Custom Number Format like ;;;"NO"
But that will only apply if there is any value in the other cells, even a space will do

 

[srinidhi]

Hui Thanks,

Actually it will be sent to students to asses their word knowledge. so the cells will be blank. Please find attached the excel.

 

[shrivallabha]

This is ( lightly ) tested basic code which should work as you want. Test it on backup.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Application.EnableEvents = False
  If Target.Column >= 3 Or Target.Column <= 6 Then
  If Trim(LCase(Target.Value)) = "yes" Then
  For Each rng In Cells(Target.Row, 3).Resize(1, 4)
  If rng.Address <> Target.Address Then rng.Value = "No"
  Next rng
  End If
  End If
Application.EnableEvents = True
End Sub

 

[srinidhi]

Thanks, for the code.
It is not working. If you have the time can you please check it I have already attached the worksheet. Thanking you in advance for this help.

 

[shrivallabha]

OK. I am uploading updated workbook. I have added "Data Validation" and an additional line of code for skipping multiple cell entries.

Enable macros and test by changing one cell at a time.