Setting up a production planning sheet with Macros

Hello! I'm trying to set up a sheet that can help me with production planning of a manufacturing facility.

Background: A multi-product manufacturing facility needs to semi-automate its production planning. There are 26 products and 8 equipment lines. A primary line and a secondary line are predefined for each product. They can only be produced in one of the two predefined lines. Therefore, a bunch of products can be produced in lines 1 and 2, a few in line 3 and 4 and so on.

Data available:
- Product names
- Primary and secondary lines each product is allocated to
- Output quantity
- Turnaround time for each product, in days

Objective:
- Based on manual selection of the drop down list, a formula must run which can select one of the two lines predefined for that product based on availability and populate the production line number in a cell automatically. The first date for production may be assumed as the first of every month
- Every time the product is selected, the cell in Start date (column E) should be automatically filled up calculating the last
- Columns that are mentioned in the sheet (in order from A to F) are batch number (manually filled), Product name (drop down list manually selected), Line number (automatically populated), starting date, date of receipt (automatically calculated from cycle time)

I have been able to populate the Line (column C) based on the selection of product in the drop down list by the VLOOKUP function, but the complication for me is that my formula doesn't factor in that IF the primary line is occupied, the secondary line should be selected. I tried used the IF formula with an IF and THEN condition, but I'm probably making a mistake in the formula.

Can the formula be made to run automatically, instead of pasting the formula from the row above and give me a production line number after the product (column C) drop down list is selected. Do I need a macro to do this? Any help on the formula and macro will be highly appreciated!

Hi Mumps1, thanks for your response. What I'm trying to do is when the product is selected from the drop down list, the first available line is populated. Logically, it happens by simultaneously looking at both lines allocated to that product and then selecting the one which is available first.

P Q and R have only one line that can be allocated to them. I have given the same line number as the secondary line because I figured that if there was a sheet-wide formula which was run, blank cells would create a problem in calculation. I hope this answers your question.