Question regarding how to search a column and print a row(s)
 

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),B2150),ROW(),"") )
Copy Q2 down to say Q100 to cover the max extent of data expected in the
source
(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:
=IF(ISERROR(SMALL(Sheet1!$Q:$Q,ROWS($A$1:A1))),"", INDEX(Sheet1!A:A,MATCH(SMA
LL(Sheet1!$Q:$Q,ROWS($A$1:A1)),Sheet1!$Q:$Q,0)))

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 criteria, i.e
those rows with values in col B 150. Result rows will be bunched neatly at
the top
---
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:
=IF(B2="","",IF(ISNUMBER(SEARCH("OK",B2)),ROW(),"" ))
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 ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Gary Braida" wrote in message
...
Hello,

I have a spreadsheet consisting of 15 columns and 100 rows. I would like
to
search a column for a specific string or based on some other criteria
(e.g.,
150) and for columns meeting the desired criteria, I would like to
print
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
getting
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
the
"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!

Regards,
Gary Braida