@bradahles That is going to require VBA. Not beginner VBA either. Not advanced, but you will have to know how to trap when data is entered, validate the source info, then manipulate the destination info.
It wouldn't be automatic, but you could use Power Query to do this on command much easier.
Original data is in an Excel Table.
Load that table into Power Query.
if the G column has "Y" in it, keep that data.
Load that result into your other sheet.
Then just hit REFRESH periodically to keep loading that data to the 2nd table. Your source data will never be impacted by this. Power Query never ever edits the source table.