Move data from one sheet to another when conditions are met

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!

Welcome to the Forum Shadd_wastaken!

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #1 requires good titles. "Is this possible?" doesn't tell us anything about your question. I have updated it for you this time because you are a new member. --6StringJazzer

You have not attached your document, only a screen shot. You will get better responses if you attach the actual document. We need to see all the formulas you are using and your conditional formatting rules. The paper clip icon does not work for attachments. To attach a file:

1. Under the text box where you type your reply click the Go Advanced button.
2. On the next screen scroll down and under the Attachments section click the Manage Attachments link, which will show a pop-up window.
3. Click the Choose File button to select a file to attached.
4. Click the Upload button to upload the file.
5. Then click the Close This Window button. Your file is now attached to your post.