Move data from one sheet to another when conditions are met

[Shadd_wastaken]

Document attached!

Good evening,
I am somewhat new to Excel; however, I have a working knowledge of Google Sheets.

EXPLANATION:
Sheet 1 and 2 both contain similar data that is conditionally formatted. (as seen). Green can be ignored, but the Red shaded of each sheet will populate on sheet 3. When / if a red shaded cell has "yes" next to it, it removes itsself from the top of sheet 3 and goes to a different section at the bottom.

so the next step lets say a few days pass and automatically john 7/28/21 would turn red and fall under Lauren on sheet 3 etc. Unless we put a "yes" beside Johns name and then it would skip to the bottom section of 3.. and so on.

Once the info on sheet 1 and 2 are deleted, the cycle ends and new info would replace that spot.

UNSURE
I can get the info to fall into sheet 3, and i can get it to disappear IF a "yes" is present. I cannot figure out how to then make it show up at the bottom and know to use the next in line.. Perhaps im just and idiot and have the wrong idea completely. Any suggestions would be helpful!