validation of cell

[gmazza76]

Afternoon,

I have a quality sheet that is filled in in a spreadsheet.
I have created a list that has "yes, no & N/A" as an option for each of the 25 questions that need to be checked.

As certain parts of the quality sheet are not always applicable i have added a question to give a yes / no answer.

When no is picked i want the 3 questions it covers to automatically go to N/A but when they are applicable ( ie "yes" is selected ) i still need them to be changed as yes / no.

I was using a formula in the validation cell as follows

=if(C6="No","N/A",=YN)
"=YN is the name of the list i want as an alternative"

But this isnt allowed and i am unsure of what to use

 

[Damon Ostrander]

Hi Gmazza76,

Yes, you can't do this with a cell formula. However, you can easily do this with a few lines of VBA code. This code will put N/A in C7:C9 if "No" is entered in C6:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Address = "$C$6" Then
      Application.EnableEvents = False
      If Target.Value = "No" Then
         Range("C7:C9") = "N/A"
      Else
         Range("C7:C9").ClearContents
      End If
      Application.EnableEvents = True
   End If
End Sub

This will not interfere with the list validation that is in C7:C9.

This code must be placed in the worksheet's code module. To do this, right-click on the worksheet's tab, select View Code, and paste the code into the Code pane.

This code can also easily be modified to monitor multiple cells (like it monitors C6) and enter N/A into other rows.

Keep Excelling.

Damon

 

[Scott Huish]

There is a way to make the only choice N/A with Data Validation if the cells is equal to NO, but it won't automatically change it to that value. Make another named range, and call it something like NA, and have this cell hold the value N/A

Then in Data Validation:
=IF(C6="NO",NA,YN)

 

[gmazza76]

Many Thanks for the reply.
Just 1 question if possible.

With the code below is it possible to add 2 variables ie

 If Target.Value = "No" Then
         Range("C7:C9") = "N/A"
      Else
         Range("C7:C9").ClearContents
      End If
Or
If Target.Value = "RDO" Then
         Range("C7:C9") = "Day Off"
      Else
         Range("C7:C9").ClearContents
      End If
      Application.EnableEvents = True

cheers