Hi, I know I haven't given enough information so I've added a bit more, please ignore any Macro in the example sheets as that was my failed attempt at trying to make one

Team Worksheet

First Name Surname
Alan A
Brian B

ACW Worksheet

First Name Surname Value
Alan A 1
Brian B 2

When I change the order of “Surname” in “Team”, this changes the order in “ACW” however not the value on the same row as the values so I end up with something like below.

First Name Surname Value
Brian B 1
Alan A 2

Is it possible to change this so that when I change the order of “Surname” in “Team” that it would show like below?

First Name Surname Value
Brian B 2
Alan A 1

Thanks, I'm sorry I didn't give enough information previously!