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.

Hi
you may have a look at the following addin:
http://www.rondebruin.nl/easyfilter.htm

--
Regards
Frank Kabel
Frankfurt, Germany
"Brad" schrieb im Newsbeitrag
...
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.

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.
.

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.
.

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.
.


.

Hi
in this case I really would try to use 'Data - Filter - autofilter'. This
should be easy to use for beginners as well

--
Regards
Frank Kabel
Frankfurt, Germany
"Brad" schrieb im Newsbeitrag
...
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.
.

You can add a new column to the move list, and check for an actor. For
example:

In cell O1, add the heading "Check"
In cell O2, type the formula: =COUNTIF(C2:E2,$I$1)0
Copy the formula down to the last row of data
In cell I1, type an actor name
Select a cell in the table, and choose DataFilterAutoFilter
From the dropdown list in the Check column heading, select TRUE
The selected actor's movies will be visible.

Brad wrote:
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.
.




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html