Autcopy selected details to another worksheet, without blank rows between items

[Michael Jones]

I have data as in the example below:

Investment	Value	Advice required?
Barclays	£100,000.00	Yes
Ist National	£50,000.00	No
Buy-to-Let Property	£325,000.00	Yes
Sky TV Shares	£1,000.00	Yes
ING Investment Bond	£50,000.00	No
ICI Shares	£30,000.00	No
MG Investment Bond	£30,000.00	Yes
Prudential Savings Bond 1	£30,000.00	No
Prudential Bond 2	£100,000.00	Yes
Total	£716,000.00

<tbody>
</tbody>

Where the answer in column C has been answered "Yes" I want to have this autocopied to another worksheet to produce the following, without any blank rows:

Investment	Value	Advice required?
Barclays	£100,000.00	Yes
Ist National	£50,000.00	No
Buy-to-Let Property	£325,000.00	Yes
Sky TV Shares	£1,000.00	Yes
ING Investment Bond	£50,000.00	No
ICI Shares	£30,000.00	No
MG Investment Bond	£30,000.00	Yes
Prudential Savings Bond 1	£30,000.00	No
Prudential Bond 2	£100,000.00	Yes
Total	£716,000.00

<tbody>
</tbody>

Can anyone solve my problem? Many thanks in advance

Kind regards

Michael

 

[estevaoba]

Array enter this and copy across and down:

[TABLE="width: 64"]
<tbody>[TR]
  [TD="width: 64"]=IFERROR(INDEX($A$2:$C$1000,SMALL(IF($C$2:$C$1000="Yes",ROW($C$2:$C$1000)-1),ROW($A1)),COLUMN(A$1)),"")[/TD]
[/TR]
</tbody>[/TABLE]