So your source data is in Sheet2, cols A to col H (say)
with key col = col H, data from row 2 down

Assume you want to scan col H for the number: 77 (say) - the criteria

In Sheet1,
In A2: =IF(Sheet2!H2=77,ROW(),"")
Leave A1 blank

In B2:
=IF(ROWS($1:1)COUNT($A:$A),"",INDEX(Sheet2!A:A,SM ALL($A:$A,ROWS($1:1))))
Copy B2 to I2. Select A2:I2, copy down to cover the max expected extent of
source data in Sheet2, say down to I200? Minimize col A. Cols B to I will
return the desired results from Sheet2, all neatly packed at the top.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
---
"OCDinNC" wrote:
Thanks so much for your reply, Max. I am pretty new to using Excel, and am
trying to fit your example to my situation. The range I need to filter is H,
so cells H2 through H145 on sheet 2 need to be scanned for the presence of a
number, and if there is one, then copy the rows containing numbers to sheet
1. Can you elaborate? Thanks!