Originally Posted by
mdmackillop
Hi Howard
Some impressive formulae, but I think this is a time when VBA is simpler to employ. Here's some worksheet event code to do the work. It's triggered when column U is changed, so be sure to enter Home score first (Checking can be added if required)
Happy to help with any questions
Regards
MD
[vba]
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim col As Long, rw As Long
Dim Home As Range, Away As Range, Base As Range
If Target.Column <> 21 Then Exit Sub
'Get lists containing teams from Column and Row
Set Home = Sheets(2).Range("Y32:Y39")
Set Away = Sheets(2).Range("AP30:AW30")
'Location of corner cell
Set Base = Sheets(2).Range("AO31")
'Find Row and Column where result is to be entered
rw = Application.WorksheetFunction.Match(Target.Offset(, -6), Home.Value, 0)
col = Application.WorksheetFunction.Match(Target.Offset(, 2), Away.Value, 0)
'Change the cell to Text format
Base.Offset(rw, col).NumberFormat = "@"
'Add the result
Base.Offset(rw, col) = Target.Offset(, -4) & " - " & Target
'Remove apostophes below to go to the cell (testing only)
'Sheets(2).Activate
'Base.Offset(rw, col).Select
End Sub
[/vba]