Help with If Function

[itsmekarak]

I may be wrong, but I'm thinking I need to write an if function to accomplish my goal but can't seem to figure it out on my own.

I have 13 columns and 26 rows. Rows list names of staff and columns list the months of the year. For each staff name listed on each row, when "yes" is entered into any one of the 12 columns (months of the year), I would like the other 11 empty columns to automatically turn to "NO". That way it prevents "yes" from being used more than once in that year.

Any and all help and suggestions are appreciated. Thank you in advance for your time!

 

[Takae]

Please copy it to worksheet specific module.
When user input "yes", other cells will change automatically.
(Staff names are in ColumnA)

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
    If Intersect(Target, Range("B2:M26")) Is Nothing Then
        Exit Sub
    Else
        If Target.Value = "YES" Then
            Range(Cells(Target.row, 2), Cells(Target.row, 13)).Value = "NO"
            Target.Value = "YES"
        End If
    End If
    Application.EnableEvents = True
End Sub

One more code.(General module)
Please run it after user input all "yes"s.

Sub yesno()
Dim rng As Range
Dim i As Long, cnt  As Long
    For i = 2 To 26
        Set rng = Range(Cells(i, 2), Cells(i, 13))
        On Error Resume Next
        cnt = WorksheetFunction.Match("yes", rng, 0)
         On Error GoTo 0
        If cnt >= 1 Then
            rng.Value = "NO"
            Cells(i, cnt + 1).Value = "yes"
            cnt = 0
        End If
    Next
End Sub

 

[L. Howard]

Give this a try, copy to the sheet module.

Where B1 to M1 are the months, and A2 to A5 are the names. I only used four rows, yours will be more I presume.

Change the RED text in the code to match your range.

Howard

Option Explicit
Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
    Dim MyCells As Range, c As Range
    Dim cRow As Long
    
    cRow = Target.Row
    
    Set MyCells = Range("[COLOR="#FF0000"]B2:M5[/COLOR]")
    
    If Application.Intersect(MyCells, Range(Target.Address)) = "Yes" Then
       
        For Each c In Range(Cells(cRow, 2), Cells(cRow, 13))
          If c <> "Yes" Then c = "No"
        Next
          
    End If
    
        Beep
End Sub

 

[AkaTrouble]

just as a non VBA/Macro option

1. i added a column in between Name and JAN called this Month (column B)

2. i selected first cell under Month (B2) clicked data tab and data validation. I then selected list. in the range i selected my month column headers jan-dec

=$C$1:$N$1

and ok

3. in cell i placed this formula

=IF($B2=C$2,"yes","no")

(note the $B2 and C$2 absolute reference) so i could copy across and down

then all your user would need to do is for employee click the drop down arrow next to Name select month, then that month becomes yes and the other 11 become no


hope this makes sense and is offered only as alternate option the codes above offer great solution without need for month column

 

[itsmekarak]

Thank you everyone for your quick replies. I apologize I am just now getting around to saying thank you. The Thanksgiving holiday has kept me running. Either way, both the Macro and Non VBA option worked. It's amazing. Thank you! Hope each of you had a great holiday as well!