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

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.

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

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
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"AM" wrote in message
...
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.

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

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 A2:D2, 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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"AM" wrote:
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.