Advanced Google Sheet Work Log Design

I need a Google Sheets expert to create a work log with two separate sheets: a Work Log and a Run Details sheet. Run Details Sheet This sheet will serve as a database for different types of runs. The layout should be as follows: Columns: Run Name: (e.g., "416b Lock Up," "CIT") Book On: (Start time, if applicable) Book Off: (End time, if applicable) Hours: (Calculated duration) Run Type: (A dropdown menu with four options: "Fixed," "Fixed Book On," "Fixed Book Off," and "Flexible") Rows: Each row will contain the details for a specific run. Example 1 (Fixed): A run with a specific start and end time. For example: "416b Lock Up" | "7:00 PM" | "10:00 PM" | "3" | "Fixed" Example 2 (Fixed Book On): A run with a fixed start time but a variable end time. For example: "CIT" | "8:00 AM" | (blank) | (blank) | "Fixed Book On" Example 3 (Fixed Book Off): A run with a variable start time but a fixed end time. For example: "St Lukes" | (blank) | "6:00 PM" | (blank) | "Fixed Book Off" Example 4 (Flexible): A run with a variable start and end time. For example: "Hall" | (blank) | (blank) | (blank) | "Flexible" Work Log Sheet This will be the main work log. It should be laid out to track a full month of work. Layout: 31 rows (for days of the month) and 7 columns. Columns: Date: (The day of the month) Run: (A dropdown menu where I can select multiple runs from the "Run Details" sheet for a single day.) Book On: (This column should automatically populate with data based on the run selection.) Manual Book On: (For entering times for "Fixed Book Off" and "Flexible" runs.) Book Off: (This column should automatically populate with data based on the run selection.) Manual Book Off: (For entering times for "Fixed Book On" and "Flexible" runs.) Hours: (This should calculate the total hours for each day.) Functionality Requirements Dynamic Data Retrieval: When I select runs in the Run column of the Work Log sheet, the Book On and Book Off columns should automatically pull the corresponding times from the Run Details sheet. Specific Auto-Populating Logic: For "Fixed" and "Fixed Book On" runs, the start time should appear in the Book On column. For "Fixed" and "Fixed Book Off" runs, the end time should appear in the Book Off column. Manual Entry Logic: For "Fixed Book On" runs, the end time should be entered manually in the Manual Book Off column. For "Fixed Book Off" runs, the start time should be entered manually in the Manual Book On column. For "Flexible" runs, both the start and end times will be entered manually in the Manual Book On and Manual Book Off columns. Total Hours Calculation: The Hours column should automatically sum the duration of all selected runs for a given day. This includes the fixed durations from the "Run Details" sheet and the manually entered times.

Project ID: 39749052