Create a Movie Collection Database in Excel

For a simple database, Excel can do a pretty good job of organizing and reporting your data. This example shows a movie collection database, but you could set up something similar to keep track of books, sales orders, or almost anything else.

In this database you can store movie data, then create a list of movies for a specific category or actor.

The Data Entry Sheet

On the data entry sheet, named Movies List, there's a table with seven columns.

Movies database data entry

A unique ID number is entered for each movie, then the title, and other information about the movie.

The database was named MovieList, using a dynamic range. In Excel 2007 you could use an Excel Table instead, and in Excel 2003 you could use an Excel List.

The Movie Selector Sheet

On the report sheet, named Select Movies, there are two drop down lists – one for Category and one for Actor. Select from either of those, and code automatically runs, to filter a list of movies that match your selection.

The yellow cells are named (SelCat and SelActor), and the block of green cells is a range named ExtractMovies.

MoviesDb02

To see the code, right-click the Select Movies sheet tab, and click View Code.

The Criteria Sheet

The code runs an Advanced Filter, just as you could do manually. There's a criteria area for each type of filter, stored on the CriteriaSel sheet. If you select a Category from the drop down list, the Category criteria range is used for the Advanced Filter. If you select an actor, the Actor criteria range is used.

MoviesDb03

The Lists Sheet

The final sheet in the workbook stores the lists that are used in the yellow drop down cells. These are pivot tables, based on the MovieList database. The pivot tables are automatically refreshed when the workbook opens, and when you deactivate the data entry sheet.

MoviesDb04

Use the Database

To use the database, enter movie information on the Movies List sheet. Then, select a category or actor, and a report is created on Select Movies sheet, with all the movies that meet the selected criteria.

The criteria sheet and Lists sheet are automatically updated, so no changes need to be made to those sheets.

Download the Sample File

To see the code and test the sample file, you can download the Excel movies database file. It's in Excel 2003 format, and zipped. The file contains macros, so you'll have to enable those to make the file work.

______________

Related Posts Plugin for WordPress, Blogger...

Share and Enjoy

  • Facebook
  • Twitter
  • LinkedIn
  • Google Plus
  • Pinterest

4 comments to Create a Movie Collection Database in Excel

  • Gregg E.

    How about a simple name list with numbered columns, having the number columns fixed while the name columns between them are sortable alphabetically in-place?

    For example, I have a folder that holds 208 discs and it came with an index sheet with six columns and 35 rows. (The last two places in column 6 are blank.)

    I made a simple table with 12 columns and 35 rows. Odd columns contain the numbers. Even columns contain the disc names.

    I want to be able to alphabetize the names in the even columns while leaving the numbers in the odd columns alone. Excel 2003 won't sort dis-continuous selections. When I select all the cells in use and sort, it scrambles up the number columns.

  • Zara

    I really am not that good at excel. But I think I will give this one a try. Thanks for sharing this!

  • jamie bainbridge

    Hi i wonder if you can help me, I absolutely love this database but i want to add a few more columns such as format (dvd, bluray), we buy, we sell, instore credit. how do i go about doing this with out spoiling the database.

  • Ahmed Fouad

    Could you let me know how you do this sheet ( Step by step 1,2,3 , .... ) i tried to make it again and i couldn't !! :(
    Thanks for your attention

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>