change value of a cell depending on multiple cell values

You cannot have a formula in H AND a value: it is one or the other.

You will need VBA for this.

Hi Doido,

Welcome to the forum.


Please try this code for sheet change event. Right click the Sheet Tab --> View Code --> and paste the following code into the opened code window --> Close the VB Editor --> Save your workbook as Macro-Enabled Workbook.

As per the code, after selecting a Team from the drop down in B12, once you input Points in C12, the code will do the calculation based on the selected Team.

See if this is something you can work with.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim rng As Range, cell As Range
Set rng = Range("B3:B6")
On Error GoTo Error
If Not Intersect(Target, Range("C12")) Is Nothing Then
   Application.EnableEvents = False
   If Target <> "" Then
      Set cell = rng.Find(what:=Target.Offset(0, -1).Value, lookat:=xlWhole)
      If Not cell Is Nothing Then
         Cells(cell.Row, "H") = Cells(cell.Row, "H") + Target.Value
      End If
   End If
   Application.EnableEvents = True
End If
Error:
Application.EnableEvents = True
End Sub

For details, refer to the attached.

Okay done. Please refer to the attached.

If that takes care of your original question, please mark your thread as Solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.

You may also say thanks to those who have put their time and efforts to help you in this forum by clicking the Add Reputation link under their posts, another way to say thanks.