I am giving another try more explicit

[dan_bucharest]

Ok. We have a set of data like this:

Excel Workbook
	A	B	C	D
1	player1	6	4	player2
2	player3	5	6	player4
3	player5	1	6	player6
4	player7	0	6	player8
5	player9	4	6	player10
6	player11	6	3	player12
7	player13	6	2	player14
8	player15	3	6	player16
AllPlayers

There are 16 sheets named player1, player2 etc. Example player1.

Excel Workbook
	A	B	C	D	E
1
2			player1
3
4
5
6
7
8
9
player1

Then when first round is played the new round is from left players. Note that player1 maybe is in column D not only in column A.

Excel Workbook
	A	B	C	D
11	player5	4	6	player1
12	player7	2	6	player5
13	player3	6	1	player11
14	player13	6	0	player16
AllPlayers

The problem is this. In cell A4 from sheet player1, we ask excel to search data in cell C2 from sheet player1 (the data is player1 and i think that cell C2 must be text) in sheet AllPlayers in A1:D8 range. If it find the data we ask excel to put this data (what is in cell C2=player1) in cell A4 and in cell B4 and C4 the score and in D4 the opponent. BUT IF IT NOT FIND IT THEN IT LOOK IN A1:A8 RANGE FROM SHEET ALLPLAYERS, SEARCH FOR DATA AND PUTS IN CELL C4 THE DATA AND IN C1, C2 AND C3 THE OTHER DATA.
Hope i make myself understood.

When new games is played like this:

Excel Workbook
	A	B	C	D
11	player5	4	6	player1
12	player7	2	6	player5
13	player3	6	1	player11
14	player13	6	0	player16
AllPlayers

then in sheet player1 in cell A5 to D5 the functions must be again entered to make another result.

Excel Workbook
	A	B	C	D
1
2			player1
3
4	player1	6	4	player2
5	player5	4	6	player1
6
player1

 

[hiker95]

dan_bucharest,

In sheet "AllPlayers", what distinguishes each round?

Can we have a screenshot of "AllPlayers" from row 1 to the last row for three rounds?

 

[dan_bucharest]

Ok. Is this important? I mean it should be known all the range from first row to the last?

Excel Workbook
	A	B	C	D
1	player1	6	4	player2
2	player3	5	6	player4
3	player5	1	6	player6
4	player7	0	6	player8
5	player9	4	6	player10
6	player11	6	3	player12
7	player13	6	2	player14
8	player15	3	6	player16
9
10
11	player5	4	6	player1
12	player7	2	6	player5
13	player3	6	1	player11
14	player13	6	0	player16
15
16
17	player7	6	5	player1
18	player13	3	6	player5
AllPlayers

 

[wsjackman]

how do you determine who will play whom in round 2 or 3?

 

[dan_bucharest]

how do you determine who will play whom in round 2 or 3?

It is random. I mean from the first match to the final are 4 rounds. The fourth round is final.
In every sheet (player1, player2 etc) must be entered 4 functions to determine if that player rested on game. If not the cells return blank.

 

[hiker95]

dan_bucharest,

How about one more screenshot of "AllPlayers" alter all four rounds.

 

[wsjackman]

do you want the computer to select the random selections for the rounds?

in the second round you had player 5 twice. Is this "oK"?
this makes a difference for adding data to the player sheets.

 

[dan_bucharest]

No. selections are made random. And random i mean put name of players on papers, put the papers in a hat and then extract the papers (like in football who is extracted first is the home team). First player is extract it is inserted in cell *A. Then the opponent in cell D etc.

 

[dan_bucharest]

Sorry. Now i see i have done a mistake. I put the players names hwo did not win. But i done it in a hurry to make an example.

Excel Workbook
	A	B	C	D
1	player1	6	4	player2
2	player3	5	6	player4
3	player5	1	6	player6
4	player7	0	6	player8
5	player9	4	6	player10
6	player11	6	3	player12
7	player13	6	2	player14
8	player15	3	6	player16
9
10
11	player6	4	6	player1
12	player8	2	6	player10
13	player4	6	1	player11
14	player13	6	0	player16
15
16
17	player10	6	5	player1
18	player13	3	6	player4
19
20
21	player4	6	4	player10
AllPlayers

 

[hiker95]

dan_bucharest,

Sample data from your earlier post, before the macro:

Excel Workbook
	A	B	C	D
1	player1	6	4	player2
2	player3	5	6	player4
3	player5	1	6	player6
4	player7	0	6	player8
5	player9	4	6	player10
6	player11	6	3	player12
7	player13	6	2	player14
8	player15	3	6	player16
9
10
11	player5	4	6	player1
12	player7	2	6	player5
13	player3	6	1	player11
14	player13	6	0	player16
15
16
17	player7	6	5	player1
18	player13	3	6	player5
19
AllPlayers

Excel Workbook
	A	B	C	D
1
2			player1
3
4
5
6
7
player1

After the macro:

Excel Workbook
	A	B	C	D
1
2			player1
3
4	player1	6	4	player2
5	player5	4	6	player1
6	player7	6	5	player1
7
player1

Excel Workbook
	A	B	C	D
1
2			player16
3
4	player15	3	6	player16
5	player13	6	0	player16
6
player16

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Press the keys ALT+Q to exit the Editor, and return to Excel.

Option Explicit
Sub UpdatePlayers()
Dim LR As Long, c As Range, rng As Range, ws As Worksheet
Application.ScreenUpdating = False
With Sheets("AllPlayers")
  For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
    If c <> "" Then
      Set ws = Sheets(c.Value)
      With ws
        LR = .Cells(Rows.Count, 1).End(xlUp).Row
        If LR < 4 Then
          LR = 4
        Else
          LR = LR + 1
        End If
        Set rng = Sheets("AllPlayers").Range(Cells(c.Row, 1), Cells(c.Row, 4))
        rng.Copy ws.Range("A" & LR)
      End With
      Set ws = Sheets(c.Offset(, 3).Value)
      With ws
        LR = .Cells(Rows.Count, 1).End(xlUp).Row
        If LR < 4 Then
          LR = 4
        Else
          LR = LR + 1
        End If
        Set rng = Sheets("AllPlayers").Range(Cells(c.Row, 1), Cells(c.Row, 4))
        rng.Copy ws.Range("A" & LR)
      End With
    End If
  Next c
End With
Application.ScreenUpdating = True
End Sub

Then run the "UpdatePlayers" macro.