Autofill sheet 2 with sheet 1

问题:

I have sheet 1 which contains:

I'll just put "Y" as "Passed", the 2nd information of Joan is not yet complete because it's not yet finish.

Name | Attendance | Final Grade | Result
Juan |     Y      |      Y      | Passed
Joan |     Y      |             | 

I create a 2nd sheet with only 1 column saying "Graduates". What I want here now is when the Column Result (D2) on Sheet 1 says that the students Passed. It will fill the Column on Sheet 2 (A2) with the name of the student that passed.

Like this:

Graduates |
Juan      |

So far, I tried auto-filling the Sheet 2 using Sheet 1 by putting =Sheet1!D2 on my cell on sheet 2 (A2), but how can I put the data under Column Name (on sheet1) on Column Graduates (on sheet2) if the Person on sheet1 has a value under the column Result?

回答1:

In sheet2's A2 use the following formula:

=IFERROR(INDEX(Sheet1!A$2:A$9999, SMALL(INDEX(ROW($1:$9998)+(Sheet1!D$2:D$9999<>"passed")*1E+99, , ), COUNTA(A$1:A1))), "")

Fill down for a large number of rows equal to the number of students in sheet1. Your results should resemble the following.

        

As soon as Joan has Passed put into column D through typing or formula, the results on sheet2 will change to something like:

        

The IFERROR function shows empty strings (e.g. "") for every cell you filled down that cannot produce a matching value.

回答2:

I solve it using this formula in Sheet2

=IF((Sheet1!D2="Passed"),Sheet1!A2,"")