I believe what you want is should be easy to do.
Based on the screenshot you share you want to add 2 new Lookup Field columns that pull the prices from the Contact List to the Master Media Plan. From there a Formula Field will do the math.
I do have a few questions, though. I'm assuming you have multiple prices to pull from. Am I correct? A price for full page and a price for 1/3 page? If that's the case, then you're gong to have to have multiple records to achieve that. I would suggest pulling your prices into a new table that references both of your existing tables.
If you'd like to talk for me to walk you through it pick a time Zaporator
I think it would be too, I'm so Excel and FileMaker oriented, that I'm likely overthinking it.
Yes, there are multiple prices. A full page ad could be Run of Book, Cover 2, Cover 4, Far Forward and myriad of other positions which all have different costs. The smaller pages for legalese range from a full page to 1/2 page, 1/3 page, 1/4 page and 1/8 page. Any combination of the full size page + legalese could be purchased.
Right now I have several test tables trying different ideas and I'm sure I can make something work, but is it the best practice? I'm going to dive in this a bit more and I might reach out after the holiday. Thanks!
I think Airtable would be more suitable than Excel, as it will be more dynamic.
A simple restructure should give you the results you want. Link the tables using the below structure (more or less).
Contact List <-> Price List <-> Media Plan
Contact List has a list of all your customers
Price List would have prices listed. Each of the variations would be 1 record, and would be tied to the customer. I would also add dates and maybe an active/inactive field. That way you can maintain historic data.
Media Plan would in turn be tied to the Price List. It would pull the prices (along with any other info you need) from the Price List using a Lookup Field.
I look forward to talking. Happy Labor Day.