Using Excel to Make a Movie Collection Spreadsheet

[jdanniel]

Hello, everyone. I’m using Excel 2010, and consider myself a novice at spreadsheet design. I don’t do anything fancy or elaborate, but I would like to ask a question or two about designing a particular kind of spreadsheet.

I’d like to design a spreadsheet that serves as my movie collection catalog. Before I continue, I know a database is probably better for this than a spreadsheet. I’ve designed a relational database for this, but I want several calculations and percentages in my catalog. Databases are much more complicated to me (especially many-to-many databases) than spreadsheets. I’m just curious to know if what I want to do is possible, aside from databases.

In other words, I hope you’ll indulge me.

Anyway…I want to design a spreadsheet with three worksheets. Worksheet #1 will have the basic details of the movie. It will have the title, its location in my house (my movies are color coded and numbered to signify where they are in my home), and a few other details, such as whether it is a foreign film, or a silent film.

Worksheet #2 is the one I’m most concerned about. I’d like this worksheet to be for actors. I want to be able to list several actors for one particular movie.

For example, the movie Jaws has three main actors: Roy Scheider, Robert Shaw, and Richard Dreyfuss.

Here’s what I want to know: Is it possible, using Excel, to link these particular names/records to a particular movie in Worksheet #1, so that if I filter Worksheet #1, the corresponding data in Worksheet #2 is filtered automatically, as well?
Or, if I do a Find for “Jaws,” can the find also find the corresponding data in Worksheet #2 as well?

If this is possible, would I have to assign a Serial Number to each movie? Would I need a column for serial numbers, and link the actors to a movie’s serial number? Or would I simply need to use the movie’s cell number?

If it is possible, is it easy, difficult, or expert-level complicated?

Lastly, the third worksheet will have various calculations and percentages, which do not need to be linked to any particular movie. I’d want a total movie count, a total Foreign movie count, a total Silent movie count, and percentages for Foreign and Silent.

For example, if I have 100 total movies, and 35 Foreign movies, the Foreign Film percentage would be 35%. And so on.

So, I want to focus on Worksheet #2 – the Actors. Again, I know and understand that a relational database is the preferable way to do this. I am simply curious to know if I can do this with Excel.

Thank you for your help and feedback, and for indulging in my curiosity. J. Danniel

[MarvinP]

Hi jdanniel,

I think you will need a newer version of excel to find "Relationships" in the tools. See if this link gets you the answer you need.

http://www.excel2013.info/relationship-between-tables/

[jdanniel]

Thank you, Marvin.

That link was indeed helpful. My copy of Office is from 2010, and I had a gut feeling it might be out of date. I have to decide whether to invest money and update it, or not. I have Access 2013.

In the meantime, I'll study that link further. Thank you again. Jd

[MarvinP]

Hi,

If you have Excel 2010 you should update your profile to show that. It now says you have Excel 2003, which doesn't have the newer features and answers might be different using this older version.

[jdanniel]

I will do that, thank you.

[jdanniel]

Greetings. I splurged, and purchased a subscription to Office 365 Personal. So, now I have the most current version of Excel, which I believe is 2016.

I now see an options for "Relationships" in the Data menu. So...now that I have the most current version of Excel (at least, I think I do), how do I go about figuring out if this feature will be applicable to what I want to do? Thank you! Jd