Wow that works great! Thanks so much.

No prob, welcome

.. but what I really need the formula to do is this...

Ah so, the *real* question surfaces.
This is a different ballgame

Try this non-array pitch:
In C4 is DV for the sheetnames as before

In D4:
=IF($C$4="","",IF(C100="","",IF(ISNUMBER(MATCH(C10 0,INDIRECT("'"&$C$4&"'!B4:B100"),0)),"",ROW())))

In E4:
=IF(ROWS($1:1)COUNT($D$4:$D$54),"",INDEX($C$100:$ C$150,SMALL($D$4:$D$54,ROWS($1:1))-3))

Select D4:E4, copy down to E54 to return the required results,
all neatly bunched at the top, viz as per your specs:
.. to return the names in the list (C100:C150) only if they
are not found in the worksheet named in cell C4 in cells B4:B100
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Klee" wrote:
Wow that works great! Thanks so much.
I've never used Indirect or Ctrl+Shift+Enter before so I'm not sure how it
works with If's. I wanted to figure out how to search in the worksheets
without confusing the reader but what I really need the formula to do is
this...

Like I said, the worksheet name is in cell C4 of the summary page (in a drop
down list) and the information that I need to compare is in cells B:4:B:100
of each worksheet.

I have a list on my summary sheet in cells C100;C150.
I would like the formula to return the names in the list (C100:C150) only if
they are not found in the worksheet named in cell C4 in cells B4:B100

Is there a way to add this into the formula that you already provided
=IF(C4="","",INDIRECT("'"&C4&"'!B4:B100")) or have I just entirely confused
you.

Thanks so much for the help!