Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summary sheet/ hide rows
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summary sheet/ hide rows
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 On Tue, 21 Nov 2006 13:28:01 -0800, 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summary sheet/ hide rows
.. 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summary Sheet help with multiple sheets | Excel Discussion (Misc queries) | |||
Hiding rows while sheet is protected | Excel Discussion (Misc queries) | |||
Daily Totals on a summary sheet | Excel Worksheet Functions | |||
resetting last cell | Excel Discussion (Misc queries) | |||
Does excel recognise names rather than cells? | Excel Worksheet Functions |