Data Transfer Among Sheets

Good morning everyone

Today I am working on expanding our automation process beyond our index match functions. I would like to know whether it is possible to move data between sheets. I seem to be a little stumped.

Ex:
I have a group of a hundred people and I split this group into Group A, Group B, and Group C.
In sheet1 I have the list of people in group A, sheet2 the list in group B, etc.
In sheet4 I have in column A the entire roster of people and in column B I have the designated group letter (A, B, C).
Is there a way to write a function so that if I change the letter in column B of sheet4, the name will move from whatever group sheet it was in, to the designated sheet? Thanks!

Ray

Thank you for the advice!
I figured I would need to do the macro but held out some hope. I came up with a similar formula and ran into the same issue of needing to move dynamically.

=INDEX(Cumulative!$A$1:$B$50,SMALL(IF(Cumulative!$B$1:$B$50 = X!$C$1,ROW($B$1:$B$50)),ROW(1:1)),1)