Duplicating Entries

I'm trying to make a simple worksheet that shows who has played who in a league and the number of times they have played. Across the top column and down the left column are the player names. When I enter the number of times Player X has played Player Y, that value should also show up in the number of times Player Y has played player X.

Mike

Hi Mike,

Sorry but not following here. Let's just use Plyr 2 (A3) as an example.

If you put a 5 in B3 which means Plyr 2 (A3) played Plyr 1 (B1) 5 times, it is a matrix so it is reversed at the same time.

Some more understanding please?

Yes, so a 5 would also go in C2 when I enter the 5 in B3 or vice-versa.

Mike

Moderator's Note: As per Forum Rule #12, please don't quote whole posts unless necessary…it's just clutter...Thanks.

Hi Mike,

I don't think you will get this done with any formula. Sounds like VBA is required.

Is this the direction you want to go and how big is this table in the real workbook?

Try this attachment with VBA behind worksheet #1.

Does this helps:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Intersect(Target, Range("B2:Z26")) Is Nothing Then Exit Sub
    Cells(Target.Column, Target.Row).Value = Target
    
End Sub

As an option

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myCol As Range
    Dim myRow As Range
    Dim my1Name As String
    Dim my2Name As String
    
    If Not Intersect(Target, Range("B2:V26")) Is Nothing Then
        If Not Cells(1, Target.Column) = Cells(Target.Row, 1) Then

            my1Name = Cells(1, Target.Column).Value
            my2Name = Cells(Target.Row, 1).Value

            Set myRow = Columns(1).Find(what:=my1Name, LookIn:=xlValues, lookat:=xlWhole)
            Set myCol = Rows(1).Find(what:=my2Name, LookIn:=xlValues, lookat:=xlWhole)

            Application.EnableEvents = False
            Intersect(myRow.EntireRow, myCol.EntireColumn) = Target.Value
            Application.EnableEvents = True

        End If
    End If
End Sub

@zbor & John,

Both of these options are great, thanks.

@Mike,

Here is a file with both options.

Originally Posted by eznoh

The worksheet would not work when I loaded another spreadsheet which records the scores.

Hard to say without seeing the offening workbook, but the macro is a worksheet change event so much be placed in the code for the sheet and the ranges need to be updated if moved from the original.

I can't see why they would...