Glad to hear it is working, albeit, after a fashion. And yes, I did
not design it to be impervious to insertions, which is something I
normally do for myself, but thought it might complicate the formulas
more than necessary. However, we can do it. Put the following formula
in sheet 2, cell A10, where row 10 is the first row you want to use on
sheet 2 initially - substitute another value for 10 if you want to put
it elsewhere initially:

=IF(COUNTIF(Sheet1!$A:$A,"hot")<ROW()-ROW($10:$10)+1,"",INDEX(Sheet1!A:A,MATCH(SMALL(She et1!$B:$B,ROW()-ROW($10:$10)+1),Sheet1!$B:$B)))

Drag down and across. Now you can insert or delete rows, except for
the first one in the list, to your heart's content, so that the list no
longer starts in row 10. You can also copy/insert a row in the middle
of the list if you wish without affecting the result.

As for the formula in words, here is my best attempt, which tends to be
from the inside out:

The expression ROW()-ROW($10:$10)+1, which occurs in two places,
generates a number which we can call N, where N is the list row number
within the list being created, e.g., the first row in the list is row
one, even though it may be on row 10 of the spreadsheet, second row is
row 2, etc. It does this by subtracting the sheet row number of the
first row in the list, ROW($10:$10), from the current sheet row number,
ROW(), and adding one.

The SMALL function uses N to generate a number, which we will call R,
and which is the Nth smallest value in the helper column in sheet 1,
e.g. if we are on the first row of the new list, it extracts the first
smallest value, which happens to be the sheet row number of the first
"hot" prospect on sheet 1. If we are on the fourth row it extracts the
fourth smallest value, which will be the sheet of the fourth hot
prospect on sheet 1, and so on.

The INDEX function uses R to index into sheet 1 and pull the value from
the Rth row in each column of sheet 1.

Finally, if N is greater than the number of hot prospects, insert a
blank (null) in the cell, else do all of the above.

To make it (slightly) more efficient, even though it may not be worth
while unless your prospects are on the thousands, put this formula
anywhere you can find to put it conveniently, EXCEPT in the the header
of your helper column in sheet 1, which must not contain a number:

=COUNTIF(Sheet1!$A:$A,"hot")

Then change the formula above to

=IF(hotcount<ROW()-ROW($10:$10)+1,"",INDEX(Sheet1!A:A,MATCH(SMALL(She et1!$B:$B,ROW()-ROW($10:$10)+1),Sheet1!$B:$B)))

where "hotcount" is an absolute reference to the cell in which you put
the COUNTIF formula.

You could also extract the ROW()-ROW($10:$10)+1 expression and place it
in, say, col A of sheet 2 but I doubt that you will notice any
difference unless you are dealing with thousands of "hot" prospects,
which, almost by defintion, could hardly be true.

Hope this helps.

Regards

Declan O'R