update one sheet based on results on another sheet

[bewise]

Hello All,

On sheet 1 i have Teams and the TeamMembers.
Sheet 2 i have Race Results which are updated automatically.
What i would hope for on sheet 3 is have result of how each team member for each team did against his team member.
Please see dummy example for better understanding.

Thanks.

[6StringJazzer]

How are you getting the result shown in Sheet3? There were 17 races; which race is this showing? Your sample data shows Person 1 vs. Person 10 as "10 to 6" but there is no race where Person 1 finished 1st and Person 10 finished 10th. I would have expected, for example, "10 to 19".

I took a guess at what you might be looking for, check this example. However, you have posted to the VBA forum and my solution uses formulas, so I'm not sure this is what you want.

[bewise]

Hello 6StringJazzer,

Thanks for your help.
I posted in VBA forum because i was not sure if formula solution was possible.

If you look at sheet3 d5 that states 10 to 6 that means person 1 finished 10 off the 16 races better than person 10.
so when the race results are loaded on sheet2 for race 17 if person 1 finishes the race in a better position that person 10 then D5 (Sheet3) will show 11 to 6.

This sheet is only a dummy workbook the original workbook the worksheet are updated with race results automatically.
I hope this might be clearer for you to understand.

Thanks Again.

[6StringJazzer]

I will take another look as time permits. Your explanation immediately above of what appears in Sheet3 is helpful.