Copy Data from Sheet1 to Sheet2 depending on input on Sheet1

Hi,

I am trying to copy data from one worksheet to another based on the input of column E. In Column E I have a drop down box with options of yes or no. If I put yes I want that entire row running from A to E to be copied to sheet2.

Can somebody please, please help me?

Regards
Jason

If you attach a sample workbook (the FAQ describes how to), I can set this up for you.

Hope this helps.

Pete

Hi,

Please find sample attached.

Regards
Jason

The attached file does this for you.

For convenience, I have inserted a new column A in both sheets and used these as helper columns - they can be hidden if you wish. In A4 of Sheet1 I've used this formula:

=IF(F4="yes",MAX(A$3:A3)+1,"-")

which is copied down to the bottom of your table. It will set up a unique sequential number for each record which has Yes in column F. Then in A4 of Sheet2 I have this formula:

=IFERROR(MATCH(ROWS($1:1),Sheet1!A:A,0),"-")

which finds the row(s) where those unique numbers occur. Then in B4 I have this formula:

=IF(OR($A4="-",$A4=""),"",IF(INDEX(Sheet1!B:B,$A4)="","",INDEX(Sheet1!B:B,$A4)))

which returns the appropriate data from column B of Sheet1. This formula can be copied across into C4:E4 to retrieve data from those columns. Finally, the formulae in A4:E4 can be copied down as far as you think you need them - I've just copied to row 20, but the hyphens help to show you how far the formulae are active for.

Then you can just change some of the entries in column F of Sheet1 and see the display on Sheet2 change automatically.

Hope this helps.

Pete