Transferring data from Sheet1 to Sheet2 based on criteria

Hello all. As per the site guidance I've had a look through some threads and couldn't find an equivalent problem. I'd be grateful if someone could point me in the right direction. Bear with me...

In the screen-grab below, Sites 1 - 4 are listed in row 10 with a series of of corresponding tasks in column B. The columns D, G, J and M show what the status of each task is in relation to each site. So far, so good.

Now, each task has a 'Status' number against it (1-5). These values are selected from a drop-down. As can be seen from the colour legend at the top of the spreadsheet, numbers 1-4 mean the task is still ongoing or outstanding. If the value of the task is '5', that means it is complete.

Spreadsheet1.jpg

What I would like the spreadsheet to do is to read across row 11 (actually rows 11 to 50 inclusive) and look for tasks which are still open (values between 1-4), THEN transfer them onto Sheet2. This is not the difficult bit, and I could probably figure it out in time. HOWEVER, what I am unable to do is to list the task in order in Sheet2.

In the example below (which I've entered manually), the information that is contained in Sheet1 has been automatically duplicated to Sheet2, based on the condition that the tasks are still open (i.e. returned a value between 1-4).

Spreadsheet2.jpg

What I'd also like the spreadsheet to do is to automatically populate the Sheet2 list when a status in Sheet1 gets updated. For example, if task 1 at site 4 gets updated to status 5 (i.e. 'Complete') then that task is removed from the list and tasks 2 and 3 are moved up a row to take its place.

I hope I've made this clear enough. Thanks in advance for any help!

Many thanks, Bernie. Can I clarify a few things before I attempt this?

Originally Posted by Bernie Deitrick

In cell AA11 of "Work List", enter the formula:

=IF(MIN(D11,G11,J11,M11)<5,ROW(),"")

and copy down to match your list on that sheet. Now You have a column of helper formulas.

What do you mean by 'copy it down'? Do you mean extend the D11, G11, J11 and M11 to read D11:D50 (for example), G11:G50, J11:J50?

Originally Posted by Bernie Deitrick

And in F8

=IFERROR(IF(INDEX('Work List'!M:M,SMALL('Work List'!$AA:$AA,ROW(A1)))<5,INDEX('Work List'!M:M,SMALL('Work List'!$AA:$AA,ROW(A1))),""),"")

Then copy those five formulas down until they return blanks.

Again, what do you mean by 'copy it down'? Sorry if this appears stupid!

Thanks

Thanks, but it's not working. I've attached the file for ease of viewing.

Thanks again for your help.

I've figured out the formulas and am almost there. However, I seem to be reading from column A1 in Sheet1, rather than A2. In other words, the formula is duplicating the number of the task, not the wording of the task itself, which is what I'm after.

Spreadsheet3.jpg

Bullseye!

Many thanks, Bernie. You're a star.