Results from one sheet to other with checking persons names

[mull21]

I have two problems with one of my competition results file.
File you can see it from here

1. On first sheet "Mens" i fill myself with results, but from this table first five results i need to have on sheet "Mens-teams". I can't figure out how to do it so that it check the persons name and then take those results. Lookup funktion in this situation is not ok as I'm sorting sheet "Mens" and then it will not understand it correct results on sheet "Mens-teams"

2. How it is possible to sort blocks of results on sheet "Mens-teams" ?

[mull21]

forum did not let me to add link to the file. How can i give the file in here?

[bakerman2]

Below the Quick Reply box click Go Advanced.

Scroll down to Manage Attachments and follow instructions.

[mull21]

heres the file

[bakerman2]

How about this. Formulas on Mens-teams.

[mull21]

did not see any added formulas for Mens-teams. Or I'm so stupid that i don't see it :D
Can you add formula example to the name Gabriel Veide. Then i can be sure where to find your added formula.

[mull21]

Sry... did notice now that you have add that formula to Mens-couples sheet. Yes that seems to work correctly. Thank you.
Any idea for my second problem... how to sort those teams or couples results as they are in kind of a blocks.

[bakerman2]

One way to do it.

[mull21]

Rank function solution just give me rank on sides... but at the end what i would like to get is the way to sort those blocks in rank order.

By the way your solution for getting result by name is super.... this works very well. Thank you!

[bakerman2]

So if I understand correctly you want to move entire blocks according to rank ?

[mull21]

yes that's correct.

[mull21]

The best way would be if results on sheet Mens is inserted then blocks on sheet Mens-couples will sort automatically based on rank.

If this is impossible (but i know nothing is not impossible) then i could sort it myself... just at the moment i don't know how to make it as regular sort options don't understand to sort blocks.

[bakerman2]

I doubt that what you're after is possible even with VBA. So this is my final offer.

The ranking is dynamic meaning it adjusts automatically when you enter points.

Columns O, P & Q are helper columns which you can hide if you want to.

[mull21]

This still is not what i was hoping to get to make easier to use my table. I did use now regular RANK function for column A and i will just manually copy-paste those blocks to right order. Not very good solution but thats the only way i could manage it at the moment.
But bakerman2... the other help you gave me was much more important to me and it helped a lot. as my competition already running then your code helped to save some time for me. Thank you.

[bakerman2]

Glad to help and thanks for rep+.

[bakerman2]

Think you're gonna like this one.
Check standing on Mens-couples. Change/Add numbers on Mens sheet so standings change.
Check Mens-couples sheet again.
This is just first draft so comments or improvements are welcome.