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

 

[dan_bucharest]

It works only a litle bit. This is the message
Runtime error 9
Subscript out of range

Then debug
Set ws = Sheets(c.Offset(, 3).Value)

 

[dan_bucharest]

End then in player1 sheet it puts 5 or 6 times the same game player1 vs player2 with same score. Is there a way to put only formulas because my professor said only formulas, we did not rich this vba code in our material.

 

[wsjackman]

if you do not have ties

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	player1	6	4	player2				0.826751	6
2	player3	5	6	player4				0.59678	5
3	player5	1	6	player6				0.750808	3
4	player7	0	6	player8				0.37515	7
5	player9	4	6	player10				0.471812	4
6	player11	6	3	player12				0.525763	1
7	player13	6	2	player14				0.128295	2
8	player15	3	6	player16				0.264633	8
9
10
11	player11	4	6	player8				0.179063	4
12	player10	2	6	player1				0.749947	2
13	player6	6	1	player4				0.281973	1
14	player13	6	0	player16				0.061026	3
15
16
17	player13	6	5	player8
18	player1	3	6	player6
19
20
21	player13			player6
Sheet2

Excel Workbook
	A	B	C	D
2			player1
3
4	player1	6	4	player2
5	player5	1	6	player6
6	player9	4	6	player10
Sheet3

 

[hiker95]

dan_bucharest,

Is there a way to put only formulas because my professor said only formulas, we did not rich this vba code in our material.

If this is a homework assignment, you should be asking your professor for assistance, or do some research, or ask for help/guidance, but, do not ask us to write the code for you.

 

[dan_bucharest]

I do not need a code only a formula. We did not reach vba code in our material. Just formulas.

 

[wsjackman]

in my post 13

to randomize

select H1:I8 and sort on H
then select h11:i14 and sort on H