One non-array formulas play ..
Assume source data is in Sheet1, cols A to O, data from row2 down
Assume the key col is col B, with the criteria: >150
In an empty col to the right, say col Q
Put in Q2: =IF(B2="","",IF(AND(ISNUMBER(B2),B2>150),ROW(),"") )
Copy Q2 down to say Q100 to cover the max extent of data expected in the
(Leave Q1 empty)
Col Q is the criteria col which will simply assign arbitrary row numbers to
lines which satisfy the criteria. Col Q's returns will be read by the
formulas in Sheet2.
In a new Sheet2
Copy > paste the same headers from Sheet1 into A1:O1
Put in A2:
Copy A2 across to O2, fill down to O100
(cover the same range as done in col Q in Sheet1)
Sheet2 will return only the rows from Sheet1 which satisfy the
those rows with values in col B > 150. Result rows will be bunched neatly at
If the criteria to be set in col Q is say:
where the text string "OK" appears in col B
Then we could put instead in Q2:
and just copy down as before
(Change SEARCH to FIND if the case is important. FIND is case sensitive)
Sheet2 will then return the desired results ..
Adapt to suit ..
GMT+8, 1° 22' N 103° 45' E
"Gary Braida" > wrote in message
> I have a spreadsheet consisting of 15 columns and 100 rows. I would like
> search a column for a specific string or based on some other criteria
> > 150) and for columns meeting the desired criteria, I would like to
> the entire row (all columns) within the same worksheet or on a new
> worksheet. Can this be done using one or more formulas but without
> into VB or fancy macros? Can this be done period? I'm looking for
> something that searches the column using the same or similar approach as
> "sumif" function but instead of summing the specificed range, I want to
> print the rows.
> Thank you in advance for your support. Please reply to Thank you very much!
> Gary Braida