Changing cell values based on selection in another cell

[JamboBob]

Hi,

I have three cells that are have data validation to show a list with Yes,No values and with conditional formatting to highlight the selected cell. If the user selects Yes in one cell I want the other two to be set to No. I tried the following formula in cell $I$6:

=IF($J$6="Yes","No",IF($K$6="Yes","No","No"))

I get the error "This value does not match the data validation restrictions defined for this cell". Any help would be appreciated.

The purpose of this is to provide a weighted score based on which cell is selected. For this reason I only want them to be able to select one cell as Yes.

 

[Joe4]

Welcome to the Board!

I think I would handle this a little differently, since you cannot have formulas and hard-coded values in the same cells. I would use some Event Procedure VBA code which runs automatically as data is being entered.

Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code in the VB Editor window that pops-up:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    Dim ct As Long
    
'   Only run on a single cell update
    If Target.CountLarge > 1 Then Exit Sub
    
'   Set range to apply to
    Set rng = Range("I6:K6")
    
'   See if current value sent to 'Yes'
    If Target = "Yes" Then
'       Count to see how many cells in range are set to "Yes"
        ct = Application.WorksheetFunction.CountIf(rng, "Yes")
'       Decide what to do in each instance
        Application.EnableEvents = False
        Select Case ct
'           If more than 1, set the value to "No"
            Case 2, 3
                Target = "No"
                MsgBox "Already a 'Yes' in the range, setting this back to 'No'"
'           If 1, set others to "No"
            Case 1
                For Each cell In rng
                    If Target.Address <> cell.Address Then cell.Value = "No"
                Next cell
        End Select
        Application.EnableEvents = True
    End If
        
End Sub

I think this will do what you want automatically.

 

[JamboBob]

Welcome to the Board!

I think I would handle this a little differently, since you cannot have formulas and hard-coded values in the same cells. I would use some Event Procedure VBA code which runs automatically as data is being entered.

Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code in the VB Editor window that pops-up:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    Dim ct As Long
   
'   Only run on a single cell update
    If Target.CountLarge > 1 Then Exit Sub
   
'   Set range to apply to
    Set rng = Range("I6:K6")
   
'   See if current value sent to 'Yes'
    If Target = "Yes" Then
'       Count to see how many cells in range are set to "Yes"
        ct = Application.WorksheetFunction.CountIf(rng, "Yes")
'       Decide what to do in each instance
        Application.EnableEvents = False
        Select Case ct
'           If more than 1, set the value to "No"
            Case 2, 3
                Target = "No"
                MsgBox "Already a 'Yes' in the range, setting this back to 'No'"
'           If 1, set others to "No"
            Case 1
                For Each cell In rng
                    If Target.Address <> cell.Address Then cell.Value = "No"
                Next cell
        End Select
        Application.EnableEvents = True
    End If
       
End Sub

I think this will do what you want automatically.

 

[JamboBob]

Hi @Joe4,

Thanks for the quick response. That works really well. Could I ask a follow up question? This works for a single row, how would I make it work for several rows (with a separator row between sections if that makes sense - see image)?

 

[Joe4]

Try this variation:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim rng2 As Range
    Dim cell As Range
    Dim ct As Long
    
'   Only run on a single cell update
    If Target.CountLarge > 1 Then Exit Sub
    
'   Set range to apply to
    Set rng = Range("I6:K100")
    
'   See if current value sent to 'Yes'
    If Target = "Yes" Then
'       Count to see how many cells in row are set to "Yes"
        Set rng2 = Intersect(rng, Rows(Target.Row))
        ct = Application.WorksheetFunction.CountIf(rng2, "Yes")
'       Decide what to do in each instance
        Application.EnableEvents = False
        Select Case ct
'           If more than 1, set the value to "No"
            Case 2, 3
                Target = "No"
                MsgBox "Already a 'Yes' in the range, setting this back to 'No'"
'           If 1, set others to "No"
            Case 1
                For Each cell In rng2
                    If Target.Address <> cell.Address Then cell.Value = "No"
                Next cell
        End Select
        Application.EnableEvents = True
    End If
        
End Sub

Note that you will just need to set this range value to include the first and last possible rows you want to include this logic in:

'   Set range to apply to
    Set rng = Range("I6:K100")

 

[JamboBob]

Try this variation:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim rng2 As Range
    Dim cell As Range
    Dim ct As Long
   
'   Only run on a single cell update
    If Target.CountLarge > 1 Then Exit Sub
   
'   Set range to apply to
    Set rng = Range("I6:K100")
   
'   See if current value sent to 'Yes'
    If Target = "Yes" Then
'       Count to see how many cells in row are set to "Yes"
        Set rng2 = Intersect(rng, Rows(Target.Row))
        ct = Application.WorksheetFunction.CountIf(rng2, "Yes")
'       Decide what to do in each instance
        Application.EnableEvents = False
        Select Case ct
'           If more than 1, set the value to "No"
            Case 2, 3
                Target = "No"
                MsgBox "Already a 'Yes' in the range, setting this back to 'No'"
'           If 1, set others to "No"
            Case 1
                For Each cell In rng2
                    If Target.Address <> cell.Address Then cell.Value = "No"
                Next cell
        End Select
        Application.EnableEvents = True
    End If
       
End Sub

Note that you will just need to set this range value to include the first and last possible rows you want to include this logic in:

'   Set range to apply to
    Set rng = Range("I6:K100")

 

[JamboBob]

Hi @Joe4,

That is perfect! Thanks a lot.

 

[Joe4]

You are welcome.