Copy certain cells in a row to another sheet, based on certain val

[AM]

I have one sheet with Twelve columns X 200 rows of data.
I want to check values in column A and Column J. If column A is (not blank)
and column J has a 1 [numeric value], then I want columns B, C, and D in the
same row copied to another sheet.

 

[Bernard Liengme]

Without using VBA it is difficult to selectively copy.
Here is a suggestion:
In A1 of Sheet2 enter: =OR(ISBLANK(Sheet1!A1),Sheet1!J1<>1)
In B1 of Sheet2 enter: =Sheet1!B1; copy this over to C and D
Copy the four formulas down 200 rows
Now in A, the ones you do not want have the value TRUE
Select everything on Sheet2 and sort by Column A
Delete the rows with TRUE in A
Delete or clear column A
best wishes

 

[Max]

Another play to try ..

Illustrated in this sample:
http://www.savefile.com/files/1000526
Show lines satisfying criteria in another sht.xls

Assume source table as per posting is in sheet: X, cols A to J, data from
row2 down, where the key criteria cols are col A and J

In another sheet: Output (say)

In A2:
=IF(AND(X!A2<>"",X!J2=1),ROW(),"")
Leave A1 blank

In B2:
=IF(ROWS($1:1)>COUNT($A:$A),"",INDEX(X!B:B,SMALL($A:$A,ROWS($1:1))))
Copy B2 to D2. Select A22, copy down to cover the max expected extent of
data in X, eg down to D200? Hide away col A. Cols B to D will return the
required results, all neatly bunched at the top.