Help with auto sort/new entered data superseding the previous one

Hello all, I'm having a trouble with auto sort.
I have Sheet 1: 1.PNG
and Sheet 2: 2.PNG

What I want is when "Fault", "yes" is entered, the Reference and Project would be copied to Sheet 2. Each "Fault" would therefore be next to a fault Number, "001","002", etc. My problem is when new "fault" is entered, if the reference number is above the previous one, it supersedes it and becomes the new 001.
3.PNG
4.PNG
I was thinking sorting the numbers by date? Or maybe make the next formula in line on sheet 2 to ignore the number of the filled in cell. Any suggestions?

The formula I used for reference is: =IF(ROWS($A4:A$4)<=$A$3,INDEX('Progress sheet'!$A$1:$A$3457,AGGREGATE(15,3,('Progress sheet'!$U$1:$U$3457="Yes")/('Progress sheet'!$U$1:$U$3457="Yes")*(ROW('Progress sheet'!$U$1:$U$3457)),ROWS($A4:A$4))),"")

Administrative Note:

We don't expect you to bump threads sooner than 24 hours after your last post or more than once a day Please remember that those who help here do so voluntarily and of their own goodwill. They live in many different time zones and many will be out at work right now if they are not asleep on the other side of the globe. This is not a paid 'service': members will help when they are ready and able to do so, and not 'on demand'. Please do not try to put pressure of time on anyone here: if you get the help you need today, that's fine, but you really should not count on it. Thanks for your understanding and patience.

Administrative note

Welcome to the forum

missed the yellow banner advising how to get answers faster by posting a sheet ?

Please take a moment to read it and attach a sheet accordingly.

Thanks you for helping us help you

This is confusing. In the sample file as well as the screen shot it appears that you want the numbering to correspond to the position of the fault. That is if the fault is in a prior row it will receive a prior fault number.
However, the description in the post indicates that the fault numbers should be assigned based on when the fault is designated as "yes".
If the former then the present in the Fault Number column (Fault sheet) could be modified, pasted into cell B4 and then copied down: =IF(C4="","",SUM(B3,1))
If the later then assigning a time stamp (Ctrl + ; then space Ctrl + Shift + ; ) and filling the Reference column (Fault sheet) using:

Formula:

Please Login or Register  to view this content.

If neither is true, please clarify and add an example to the file.
Let us know if you have any questions.

You're Welcome. I believe that automatically assigning a timestamp is better accomplished using VBA. You may want to request that a moderator (i.e. send P.M. to Pepe) move this thread to the Excel Programming / VBA / Macros forum so that a contributor with those skills will be more likely to help.
I hope that you have a blessed day.

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. It would be helpful to those whom in the future are facing a similar issue, if you could post an sample that demonstrates your solution. I hope that you have a blessed day.