Autopopulate Cell on another sheet, based on value of cell in another

[surkdidat]

Is there anyway I can populate the data from Sheet 1 into Sheet2 based on the value of a cell in the row on Sheet1

For instance Sheet1, Column D, will have Yes or No in. Column A will have a unique reference number.

If I have

--A-- |--B--
12345| YES
23456|YES
34567|NO
45678|NO
56789|YES

In Sheet2, I want

--A--|
12345
23456
56789

(So it is not taking over the rows with "no" in.)

EDIT : It would be helpful if this is automated, and does not rely on the user having to click on a button or something similar, thanks )

 

[Special-K99]

in Sheet2!A1
=IFERROR(INDEX(Sheet1!$A$1:$A$5,AGGREGATE(15,6,ROW($A$1:$A$5)/((Sheet1!$B$1:$B$5="YES")),ROWS(A$1:A1))-(1-1),1),"")
and copy down for as many rows as you have on Sheet1 Column A

 

[surkdidat]

Thankyou for this - however, one problem I have is that in the above rows 1, 2 and 5 are Yes, and 3 and 4 are No. If row 3 or 4 changes to YES it puts it (logically) in order. However, where on Sheet2 I will have comments/dropdowns etc against the references (that are populating) then it throws everything out. Is there anyway I can get around this?

in Sheet2!A1
=IFERROR(INDEX(Sheet1!$A$1:$A$5,AGGREGATE(15,6,ROW($A$1:$A$5)/((Sheet1!$B$1:$B$5="YES")),ROWS(A$1:A1))-(1-1),1),"")
and copy down for as many rows as you have on Sheet1 Column A

 

[Special-K99]

"where on Sheet2 I will have comments/dropdowns etc against the references"

You need to explain where you actually have the comments/dropdowns

Can you supply some example data?

 

[surkdidat]

OK, will do my best!!

So, if in sheet 1 I mark it as "YES" it copies the reference into Sheet2, no problem.

In Sheet 2 I have comments I input based on the reference.

So, for example Sheet1 has references 1,2,3,4 and 5.

At the moment, References 1 2 and 5 state YES in Sheet1

In sheet2, I have comments such as a date, and that if it is shipped, a day it has been delivered etc.

In the meantime, Reference 3 is changed to YES

In sheet2, this now has references 1 2 3 and 5.

However, the manual comments input do not move.

So, if I had a date for reference 5 (08/04/19 in one cell, and SHIPPED in the next one along), when reference 4 is added, the spreadsheet leaves these comments in that line, so it looks like they relate to reference 4 rather than reference 5

"where on Sheet2 I will have comments/dropdowns etc against the references"

You need to explain where you actually have the comments/dropdowns

Can you supply some example data?

 

[Special-K99]

impossible unless the comments are on sheet 1 in which case copy the formula and change the index

=IFERROR(INDEX(Sheet1!$A$1:$A$5...

to the comments column