Associate a column data to another column data from a worksheet to another

Hi!

I've been trying to find a function to have an association made from a worksheet to the other. To help you all understand the situation, I've attached an image below.

Here is what I would like to do:

- In the "Shots" Worksheet, in Column C, I have a dropdown list created from the "Artists" Worksheet. I want to pick an artist name from that dropdown list.
- Then, when the option is selected from that dropdown list, in "Shots" Worksheet, Column I ("Cost"), I would like to display a multiplication of:

Time Total (Shots Worksheet, Column H) x (Cost (Shots Worksheet, Column I) x Selected Artist Hourly Rate (Artists Worksheet, Column C)

That way, when the artist is chosen and that the required amount of time is entered, I automatically get the cost value based on the hourly rate of that artist.

Anyone knows how to do this? I'm stuck...

Thanks!


EXCEL_Screenshot.jpg