Conditionally copying information from one sheet to another

I would like to auto populate the cells on a different sheet (same file) when the word COMPLETE is entered in to the final sell of another sheet

In the attached example I have 2 sheets "INTAKE" and "BUILD" I would like for the information on from the INTAKE sheet to auto populate the BUILD sheet when the work COMPLETE is entered in the last cell of the INTAKE sheet.

Thank you for your help

https://www.youtube.com/watch?v=tqCEY5YMyqw

Is this of any help to you?

Paste this on the Build Worksheet Cell A2 and drag down. Then drag over to column D.

Formula:

Please Login or Register  to view this content.

You can change the range that you are looking from by changing A$2:A$500 to A$2:A$###, you just pick the number to type in and etc.

I assumed that Column "E" is something you manually wanted to enter data into.

Here is your file with the formulas

Sorry, forgot this is an array Formula. Here is your file back. Just make sure to hit Control+Shift+Enteron the formula