Trying to make an Excel Spreadsheet for my Movie Collection...

[Farli]

I was wondering if anyone can help me with this. I want to be able to make a spreadsheet that I can put in name, rating (i/e G, PG, R ect.), starring actor/ess, and genre. I want it so if I click on say genre it is organized then by genre. I have Microsoft Office Excel 2007. thanks so much!

 

[venkat1926]

f I click on say genre it is organized

It is not clear what you mean
see the sample sheet below called sheet1
right click sheet 1 tab and click view code and copy paste this event code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column<> 4 Then Exit Sub
Worksheets("sheet2").Cells.Clear
Dim x As String
x = Target.Value

ActiveSheet.UsedRange.AutoFilter
ActiveSheet.UsedRange.AutoFilter field:=4, Criteria1:=x
  ActiveSheet.UsedRange.Copy Worksheets("sheet2").Range("a1")
  ActiveSheet.UsedRange.AutoFilter
  

End Sub

now click any genre x , w or c in column D . and then see sheet 2. do you get what you want. experiment with another genre in column D. Adapt this for your workbook.
post feedback.

Book2
	A	B	C	D
1	name	raging	actors	genre
2	a	g	q	x
3	s	g	w	c
4	d	g	e	v
5	f	pg	r	c
6	g	g	t	x
7	h	pg	y	x
8	j	r	u	c
9	k	pg	i	v
10	l	g	o	c
Sheet1

 

[Farli]

I cannot find any tabs on Office Excel 2007. any idea where I can find it?

 

[softwareguru]

This is quite simple. Make up your spreadsheet with all of your catagories.

Make a copy for each search criteria ie, one for actor, one for genre, etc.

Now, go to each sheet and set it up. On the ribbon go to the Data Tab and click on the filter. Set the filter for actor.

Do the same thing for each sheet.

The next time you need to add data make sure you select "ALL" of the sheets and all will continue to work well together.

Good luck......... I love Excel 2007

 

[venkat1926]

tab is the name of the sheet given below like sheet1 sheet 2 sheet3 etc
I have not seen excel 2007

 

[scottylad2]

Here's some VBA for doing some of the above

Sub ExtractTown()
'
' ExtractTown Macro
'

'
Range("I6:L6").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("H5").Select
Range("D6:G35").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"D3:G4"), CopyToRange:=Range("I6:L6"), Unique:=False
Range("B6").Select
ActiveWindow.SmallScroll Down:=-6
End Sub
Sub FilmExtract()
'
' FilmExtract Macro
'

'
Range("A4:E4177").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("Sheet8!Criteria"), CopyToRange:=Range("G4:K4"), Unique:=False
Range("E2").Select
Selection.ClearContents
Range("D2").Select
ActiveCell.FormulaR1C1 = ""
Range("E2").Clear
Range("F2").Select
End Sub
Sub FilmFinder()
'
' FilmFinder Macro
'

'
Columns("A:E").Select
Columns("A:E").EntireColumn.AutoFit
Columns("G:K").Select
Columns("G:K").EntireColumn.AutoFit
Range("M16").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("A4:E2001").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("A1:E2"), CopyToRange:=Range("G4:K4"), Unique:=False
ActiveWindow.SmallScroll Down:=-18
Columns("G:K").Select
Columns("G:K").EntireColumn.AutoFit
Range("F3").Select
End Sub

 

[scottylad2]

The above does and advanced filter copy and paste. so in my list, i type 18 and all the 18 reted films are listed after i press submit. Not got time to show the whole table, but my criterias are actors, co starts age groups and of course the film titles. Using the wildcard also means i can search from as little info as a single letter, i.e everything begingin or contain ing *j* brings everything with a j to my list.

If you send me a private message email i'll send you a copy of the sheet with all the formulas. Works a treat.

but can't do it just now as i'm off to work

 

[scottylad2]

Excel Workbook
	A	B	C	D	E
1	TITLE	STARRING	CO-STARRING	GENRE	RATING
2				Adventure	15
3	Enter search criteria n the boxes above, either film title, movie starts or just click in the Genre or Ratings boxes for a drop down and search those by clicking Find Film to the right
4	TITLE	STARRING	CO-STARRING	GENRE	RATING
5	28 Days	Sandra Bullock	Viggo Morgensten	Romance	15
6	Billy Elliot	Julie Walters	Gary Lewis	comedy	15
7	Bridget Jones	Renee Zellwegner	Hugh Grant	Romance	15
8	Bridget Jones Diary	Renee Zellwegner	Hugh Grant	Romance	15
9	Brokeback Mountain	Heath Ledger	Jake Gyllenhaal	Romance	15
10	Bruce almighty	Jim carey	Morgan Freeman	comedy	12
11	Dreamcatcher	Morgan Freeman	Donnie Whalberg	Horror	15
12	Failure to Launch	Mathew McConaughey	Jessica Parker	comedy	12
13	Fallen	Denzel Washington	John Goodman	Chiller	15
14	Harry Potter and the Chambers of secrets	Daniel Radcliffe	Robbie Coltrane	Adventure	PG
15	Harry Potter goblet of fire	Daniel Radcliffe	Robbie Coltrane	Adventure	12
16	Harry Potter prisoner of Azkaban	Daniel Radcliffe	Robbie Coltrane	Adventure	PG
17	High Crimes	Ashley Judd	Morgan Freeman	Thriller	12
18	Hot Fuzz	Simon Pecc	Nick Frost	comedy	15
19	Ice age 2	cartoon characters	Animated	Cartoon	U
20	Identity	John Cusack	Ray Liotta	Horror	15
21	Indiana Jones Trilogy	Harrison Ford	Cate Blanchett	Adventure	PG
22	Just Friends	Ryan Reynolds	Amy Smart	comedy	12
23	King Kong	Naomi Watts	Jack Black	Adventure	12
24	Lethal Weapon	Mel Gibson	Danny glover	Thriller	18
25	Lethal Weapon 2	Mel Gibson	Danny glover	Thriller	18
26	Lethal weapon 3	Mel Gibson	Danny glover	Thriller	15
27	Lethal weapon 4	Mel Gibson	Danny glover	Thriller	15
28	Love Actually	Hugh Grant	Emma Thomson	comedy	15
29	Madagascar	cartoon characters	Animated	Cartoon	U
30	Meet the Fockers	Rebert de Nero	Ben Stiller	comedy	12
31	Monsters Inc	cartoon characters	Animated	Cartoon	U
32	Mummy collection	Jet Li	Brendan Fraser	Adventure	15
33	Murder by Numbers	Sandra Bullock	Jeff Stott	Chiller	15
34	O Brother where art thou	George Clooney	John Turturro	comedy	15
35	Oceans Eleven	George Clooney	Matt Damon	comedy	12
36	Oceans Thirteen	George Clooney	Brad Pitt	comedy	PG
37	Oceans Twelve	George Clooney	Brad Pitt	comedy	12
38	Over the Hedge	Dreamworks	Animated	Cartoon	U
39	Phonebooth	Colin Farrell	Forrest Whittaker	Chiller	15
40	Pirates of the Carribean	Johhny Depp	Orlando Bloom	comedy	12
41	Robbie Williams	Robbie Williams	The Band	Music	U
42	Shrek 2	Shrek	Donkey	comedy	U
43	Shrek 3D	Shrek	Donkey	comedy	U
44	Shrek the Third	Shrek	Donkey	comedy	U
45	Sleepers	Kevin Bacon	Robert de Niro	Thriller	18
46	Starskey & Hutch	Ben Stiller	Owen Wilson	Adventure	15
47	The Da Vinci Code	Tom Hanks	Ian Mckellen	Thriller	12
48	The Devil Wears Prada	Meryl Streep	Anne Hathaway	Romance	12
49	The life of Brian	Monty Python	Eric Idle	comedy	15
Movie list

first table like this and a second one on the same page, use the exact same cells and you'll just have to copy an past the VBA code

Excel Workbook
	G	H	I	J	K
3
4	TITLE	STARRING	CO-STARRING	GENRE	RATING
5	Mummy collection	Jet Li	Brendan Fraser	Adventure	15
6	Starskey & Hutch	Ben Stiller	Owen Wilson	Adventure	15
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
Movie list

and copy this code into a macro module as is

Sub FilmFinder()
'
' FilmFinder Macro
'

'
Columns("A:E").Select
Columns("A:E").EntireColumn.AutoFit
Columns("G:K").Select
Columns("G:K").EntireColumn.AutoFit
Range("M16").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("A4:E2001").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("A1:E2"), CopyToRange:=Range("G4:K4"), Unique:=False
ActiveWindow.SmallScroll Down:=-18
Columns("G:K").Select
Columns("G:K").EntireColumn.AutoFit
Range("F3").Select
End Sub

which should be pasted into myworkbook

and the following into the Modules


Sub FilmExtract()
'
' FilmExtract Macro
'

'
Range("A4:E4177").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("Sheet8!Criteria"), CopyToRange:=Range("G4:K4"), Unique:=False
Range("E2").Select
Selection.ClearContents
Range("D2").Select
ActiveCell.FormulaR1C1 = ""
Range("E2").Clear
Range("F2").Select
End Sub
Sub FilmFinder()
'
' FilmFinder Macro
'

'
Columns("A:E").Select
Columns("A:E").EntireColumn.AutoFit
Columns("G:K").Select
Columns("G:K").EntireColumn.AutoFit
Range("M16").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("A4:E2001").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("A1:E2"), CopyToRange:=Range("G4:K4"), Unique:=False
ActiveWindow.SmallScroll Down:=-18
Columns("G:K").Select
Columns("G:K").EntireColumn.AutoFit
Range("F3").Select
End Sub

 

[scottylad2]

in Row 2 there are dropdown menus there which are data validation lists
again copy to your sheet with the exact cells and your code will run ok

Excel Workbook
	M	N
4	Comedy	12
5	Adventure	15
6	Horror	18
7	Chiller	PG
8	Thriller	U
9	Romance
10	Music
11	Cartoon
Movie list