.. trying to write an "if" statement that only copies those cell
that meet the criteria but if they don't, hide the row.

Here's a formulas play which could deliver the equivalent of the above ..

Assume source data below in sheet: X,
cols A to C, data from row2 down to a max expected row100 (say),
where the key col = col A (Code)

Code Field1 Field2
1111 Data1 Text1
1112 Data2 Text2
1112 Data3 Text3
1113 Data4 Text4
1111 Data5 Text5
1112 Data6 Text6
etc

In your summary sheet: Y (say),

Assume A2 will house the input for the code of interest, eg: 1112
Place the labels in C1:E1 : Code, Field1, Field2

Put in B2:
=IF($A$2="","",IF(X!A2=$A$2,ROW(),""))
Leave B1 blank

Put in C2:
=IF(ROW(A1)COUNT($B:$B),"",INDEX(X!A:A,SMALL($B:$ B,ROW(A1))))
Copy C2 to E2

Select B2:E2, copy down to cover the max expected extent of data in X, ie
down to E100. Hide away col B. Cols C to E will return the required results,
ie only the lines for the code input in A2: 1112 from X, with all results
neatly bunched at the top, viz. for the sample data, it'll appear as:

Code Field1 Field2
1112 Data2 Text2
1112 Data3 Text3
1112 Data6 Text6

And if we change the code in A2 to: 1111,
we'd get:

Code Field1 Field2
1111 Data1 Text1
1111 Data5 Text5

and so on ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Sally in Toronto" wrote:
Hi!

I am trying to produce a summary worksheet that automatically only contains
rows from another worksheet that meet a certain criteria.
I have been trying to write an "if" statement that only copies those cell
that meet the criteria but if they don't, hide the row.

This is a constant recurring task for me, so I don't want to have to
manually run macros and/or filters every time I want to look at the summary
sheet.

This means every cell must have an "if" formula.

I have seen a number of VBA scripts (in other messages) to hide rows but can
I enter them as part of a formula?

I hope I have explained this properly. Thanks for any help.

Sally.