Merge data and expand records based on specific criteria

[TjMc]

I have hit a snag and I am up against the wall. I have been tasked by my employer to develop a capital estimate planning tool. In this tool I am attempting to pull data from an Excel data entry Table (Table1) and have the records merge into an analysis Table (Table3) and expand a single record based on specific criteria. I am not even sure if this is possible. This initial action will populate the analysis table that will draw from the model's data entry Table and calculate values for the estimates. These Tables will be used to develop capital ROM estimates. Data Table1 has 13 Fields. Pulling the first 7 Fields from the data Table and inserting into the analysis Table are where I am having trouble. Note, in the analysis Table and beginning with the Start Date Field, those are calculated fields. I can create the functions for the model to work if I enter either the ID or the Site & Description manually; I would prefer for the analysis table to transfer the ID, Site, and Description automatically when data in data Table1 is entered fields and create the additional records for specific to the "Work" field as described below. The data entry Table1 and analysis Table3 Fields are:
DataTranferToAnalysisLogic.png
Specifically, I want the analysis Table to look at the Work Field in data Table1 and if Work is = "Const.", 4 project records are created and inserted in the Work Field are "Const.", "01.AE(D)", "02.AE(CS)", "03.CM" are inserted respectively. Also, if the CEQA Field in the data Table is "Yes", the analysis Table inserts another project record and the Work Field is "00.CEQA." From this I can run the calculation in the remainder of the analysis Table.

I understand this task would be more effective in a relational database like Access of SQL; however my employer prefers is do this in Excel. Also, I do not understand VBA syntax or programming. So such a solution utilizing VBA would not be helpful at this time. Attached is a copy of the Excel File and a JPEG of what I am attempting to achieve.

[TjMc]

Given the number of views resulting in no replies, it would appear I may have not properly posted my request. I am new to the forum and this is my first post. So, if there is something I need to change in my post that might help to solicit assistance, please let me know. I have been working on this model for 9-months gathering the data and building the formulas and filters. I have spent the last 45-days trying to solve the question I posted. Any advice on my post, critique on the quality of my post or other information would be helpful. Thank you.