Copy certain cells dependant on cell value

[bigginge]

Good afternoon,

Can you please help.

I have a infrastructure fault spreadsheet, which shows all faults in my building. It has 2 tabs one named Fault and one named EWP. Within the fault tab, there is a column W which states if a job is over due "X" or complete "Y". In What I am after is to copy the data in columns A-K dependant if column W is "X".

I would be grateful if anyone can help.

 

[Special-K99]

Copy them where?

VBA or formula?

 

[bigginge]

Sorry, usual me forgetting to put in half the info.

I want the data from columns a to k on the faults tab to the same columns on the EWP tab, dependent on fault tab column w. The 2 tabs have the same layout and column headings.

I am not fussed if it is VBA or formula, whatever can do the job.

 

[Special-K99]

This may be slow as its an array formula

With data in Faults!A2:K1000

in EWP!A2
=IFERROR(INDEX(Faults!$A$2:$K$1000,SMALL(IF((Faults!$W$2:$W$1000="X"),ROW($A$2:$A$1000)),ROW(A1))-(ROW(A$2)-1),COLUMN()),"")
Array formula, use Ctrl-Shift-Enter
copy across to EWP!K2

Then copy EWP!A2:K2 down for as many rows as you have on the Faults tab