Declan - Thank you so much for your help - this is fab!! I never thought
that I could make sense of this at all. I would like to make it more
efficient, if possible.

I think I prefer Option2, though I would like to be able to have the
solution start on any row in Sheet 2 (not necessarily on row 2). At the
moment it looks to me as if Row 1 has to remain blank (no data) and if I try
to insert rows above Row 1, therefore moving my solution down the page - it
disappears.

I am planning to use this to bring together data from 9 different workbooks
- it is in a sales enviroment and all I am interested in reviewing is the
"Hot" prospects! It works great capturing data from another workbook.

I am fascinated to understand what the word "SMALL" does in the formula - I
have tried to "read" the formula to make sense of it - I get some of it but
much is way beyond my level of understanding. If I could push you to write
out the formula in words I would be so grateful.

Thank you again for all your help and kind regards,

Brian

"DOR" wrote:

Brian,

One way is to put the following array formula in every column of the
second sheet corresponding to a column in sheet1 and drag/copy it down,
assuming the temperature column is column A, headers in row 1 and 20
data rows sheet 1, and headers in row 1 of sheet 2.

In sheet 2 A2 put:

=IF(COUNTIF(Sheet1!$A$2:$A$21,"hot")<ROW(1:1),"",I NDEX(Sheet1!A:A,SMALL(IF(Sheet1!$A$2:$A$21="Hot",R OW(Sheet1!A$2:A$21),""),ROW(1:1))))

Confirm with Ctl-Shift-enter.

Another, more processing time-efficient approach, is to put a helper
column in Sheet1, say, column B and in B2 put:

=IF(A2="hot",ROW(),"") and copy down.

Then in A2 of sheet 2, put

=IF(COUNTIF(Sheet1!$A:$A,"hot")<ROW(1:1),"",INDEX( Sheet1!A:A,MATCH(SMALL(Sheet1!$B:$B,ROW(1:1)),Shee t1!$B:$B)))

and copy down - this is not an array formula, so you just enter it
normally. Just make sure you don't put a number in sheet1 B1.

You can now delete any columns in sheet2 that you don't need.

Adjust row numbers to suit your layout.

There are ways to make it more efficient - if you are interested, just
respond.

HTH

Declan O'R