Linking SharePoint lists and showing all instances of an item

Hi, 

I have a list called ‘Clip Names’

Columns

1. Title = ‘Clip Name’

I then want to have multiple other lists which have a ‘Clip Name’ column which is a lookup to the ‘Clip Name’ column in the ‘Clip Names’ list.

For example:

Columns

1. Title = Production
2. Lookup = 'Clip Names' List / ‘Clip Name’ column
3. Timecode IN
4. Timecode OUT

I then in any of these lists (or just the Clip Names list if need be), want to be able to click a clip name and it displays what lists this clip name exists in, and the other information on the row of those various lists that that particular clip occurs in.

At the moment when you click on a clip name it just shows what is on that clip name’s row within it’s list master list so to speak.

My ultimate goal is so you can easily see what clips have been used on what productions, along with the timecodes of the clips.

Any help would be greatly appreciated.

Thanks,

Rob