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

Farli

New Member
Joined
Apr 2, 2009
Messages
2
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!
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
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

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
ABCD
1nameragingactorsgenre
2agqx
3sgwc
4dgev
5fpgrc
6ggtx
7hpgyx
8jruc
9kpgiv
10lgoc
Sheet1
 

Farli

New Member
Joined
Apr 2, 2009
Messages
2
I cannot find any tabs on Office Excel 2007. any idea where I can find it?
 

softwareguru

Board Regular
Joined
Dec 3, 2007
Messages
63
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

Well-known Member
Joined
Aug 21, 2005
Messages
4,824

ADVERTISEMENT

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

scottylad2

Well-known Member
Joined
Feb 2, 2009
Messages
1,919
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

Well-known Member
Joined
Feb 2, 2009
Messages
1,919

ADVERTISEMENT

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

Well-known Member
Joined
Feb 2, 2009
Messages
1,919
Excel Workbook
ABCDE
1TITLESTARRINGCO-STARRINGGENRERATING
2Adventure15
3Enter 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
4TITLESTARRINGCO-STARRINGGENRERATING
528 DaysSandra BullockViggo MorgenstenRomance15
6Billy ElliotJulie WaltersGary Lewiscomedy15
7Bridget JonesRenee ZellwegnerHugh GrantRomance15
8Bridget Jones DiaryRenee ZellwegnerHugh GrantRomance15
9Brokeback MountainHeath LedgerJake GyllenhaalRomance15
10Bruce almightyJim careyMorgan Freemancomedy12
11DreamcatcherMorgan FreemanDonnie WhalbergHorror15
12Failure to LaunchMathew McConaugheyJessica Parkercomedy12
13FallenDenzel WashingtonJohn GoodmanChiller15
14Harry Potter and the Chambers of secretsDaniel RadcliffeRobbie ColtraneAdventurePG
15Harry Potter goblet of fireDaniel RadcliffeRobbie ColtraneAdventure12
16Harry Potter prisoner of AzkabanDaniel RadcliffeRobbie ColtraneAdventurePG
17High CrimesAshley JuddMorgan FreemanThriller12
18Hot FuzzSimon PeccNick Frostcomedy15
19Ice age 2cartoon charactersAnimatedCartoonU
20IdentityJohn CusackRay LiottaHorror15
21Indiana Jones TrilogyHarrison FordCate BlanchettAdventurePG
22Just FriendsRyan ReynoldsAmy Smartcomedy12
23King KongNaomi WattsJack BlackAdventure12
24Lethal WeaponMel GibsonDanny gloverThriller18
25Lethal Weapon 2Mel GibsonDanny gloverThriller18
26Lethal weapon 3Mel GibsonDanny gloverThriller15
27Lethal weapon 4Mel GibsonDanny gloverThriller15
28Love ActuallyHugh GrantEmma Thomsoncomedy15
29Madagascarcartoon charactersAnimatedCartoonU
30Meet the FockersRebert de NeroBen Stillercomedy12
31Monsters Inccartoon charactersAnimatedCartoonU
32Mummy collectionJet LiBrendan FraserAdventure15
33Murder by NumbersSandra BullockJeff StottChiller15
34O Brother where art thouGeorge ClooneyJohn Turturrocomedy15
35Oceans ElevenGeorge ClooneyMatt Damoncomedy12
36Oceans ThirteenGeorge ClooneyBrad PittcomedyPG
37Oceans TwelveGeorge ClooneyBrad Pittcomedy12
38Over the HedgeDreamworksAnimatedCartoonU
39PhoneboothColin FarrellForrest WhittakerChiller15
40Pirates of the CarribeanJohhny DeppOrlando Bloomcomedy12
41Robbie WilliamsRobbie WilliamsThe BandMusicU
42Shrek 2ShrekDonkeycomedyU
43Shrek 3DShrekDonkeycomedyU
44Shrek the ThirdShrekDonkeycomedyU
45SleepersKevin BaconRobert de NiroThriller18
46Starskey & HutchBen StillerOwen WilsonAdventure15
47The Da Vinci CodeTom HanksIan MckellenThriller12
48The Devil Wears PradaMeryl StreepAnne HathawayRomance12
49The life of BrianMonty PythonEric Idlecomedy15
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
GHIJK
3
4TITLESTARRINGCO-STARRINGGENRERATING
5Mummy collectionJet LiBrendan FraserAdventure15
6Starskey & HutchBen StillerOwen WilsonAdventure15
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

Well-known Member
Joined
Feb 2, 2009
Messages
1,919
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
MN
4Comedy12
5Adventure15
6Horror18
7ChillerPG
8ThrillerU
9Romance
10Music
11Cartoon
Movie list
 

Watch MrExcel Video

Forum statistics

Threads
1,123,139
Messages
5,599,958
Members
414,352
Latest member
macquarie_jchan58

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top