Hi!

No problem, doesn't require an on the fly formula each
time.

Assume your Sheet1 is the 'database'.
Column headers are in row 1, A1:H1.
Movie titles in column B.
Actors in columns C:H.
The entire range of the database is A1:H400.

On Sheet2 is where you want to extract your data.
Sheet2 A1 is where you enter your search criteria.

Now, enter this array formula with the key combo of
CTRL,SHIFT,ENTER and copy it down to enough rows so that
all the possible matches found will be returned. I guess
that would be a judgement call on your part. If you have
400 movies, how many times will even the most popular
actor appear in 400 movies?

=IF(ISERROR(INDEX(Sheet1!$B$2:$B$400,SMALL(IF(Shee t1!
$C$2:$H$400=$A$1,ROW(INDIRECT("A$1:A$"&COUNTA(Shee t1!
$A$2:$A$500)))),ROW(1:1)))),"",INDEX(Sheet1!
$B$2:$B$400,SMALL(IF(Sheet1!$C$2:$H$400=$A$1,ROW(I NDIRECT
("A$1:A$"&COUNTA(Sheet1!$A$2:$A$500)))),ROW(1:1))) )

Don't let this formula "scare" you. There's nothing to it,
really.

If this seems to be too much for you, I can either put
together a sample file for you to study or if you'd like,
you can email a copy of your file and I'll see what I can
do with it.

If you go the email option you'll need to post an address
for me to contact you. My address as posted is bogus.

Or, you just might want to use filters!

Biff

-----Original Message-----
Thanks for the quick feedback (much quicker than I
anticipated), but it won't
work. EasyFilter, at least in this iteration,
specifically precludes
multiple column entries. I also wanted to clarify what
I'm looking to do.
The spreadsheet with the movies contains 12 actor
columns. When entering
movie data into each row, the actors are entered in order
of billing. Using
Kevin Spacey as an example, three movies he appeared in
would be entered like
this:

MODE TITLE ACTOR_1
ACTOR_2 [...] ACTOR_6
DVD AMERICAN BEAUTY SPACEY
DVD NEGOTIATOR, THE JACKSON SPACEY
DVD USUAL SUSPECTS, THE BALDWIN BYRNE [...]
SPACEY

Currently, there are only 400 movies entered, but it's
still a little
unwieldy. If I want to do a 'Kevin Spacey' movie
marathon, how do I
-QUICKLY- hide all movies WITHOUT Spacey in them? It
cannot be a formula
entered on the fly each time, as I need to make it idiot
proof for non-excel
users. It can be a formula or non-VBA macro on another
sheet in the
workbook, where all that needs to be entered is the
search string and and
keystroke (or button).

Thanks again

"Biff" wrote:

Hi!

This is relatively easy to do with formulas but try
Frank's suggestion first and see if that's what you
really
want.

If you decide you want to go the formula route, just
post
back and "we'll" fix you right up!

Biff

-----Original Message-----
My mother created at a spreadsheet to document all her
movies. The columns
include TITLE, GENRE, RELEASE DATE, and 12 separate
columns for actors
(ACTOR_1, ACTOR_2, etc.). She has a second sheet in
the
workbook, with all
the actors listed. This has 3 columns (Name, Movie
Title, and Release Date).

I'm adding my collection to her 'database' and she's
mad
that I won't enter
anything on the "ACTORS" sheet. If I'm looking for a
specific actor, I just
use FIND, and FIND ALL to see what movies we have that
they were in. The
question; is there a way to export the find results
to a
separate sheet? Or
a function/tool in Excel that would search across
mutliple columns (for all
instances of Kevin Spacey) and easily show what movies
we
have that have him?
Does this question make sense?

Thanks to anyone who can help.
.


.