Summary sheet/ hide rows

[Sally in Toronto]

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.

 

[Gord Dibben]

Sally

Formulas cannot do formatting such as hiding rows.

Formulas pull values and show those values.

You could perhaps use Conditional Formatting to change the font color to white
if data does not meet specs.

Or you would have to use VBA.......either a macro or event code.


Gord Dibben MS Excel MVP

 

[Max]

.. 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 ..