Smaller tables feeding into larger tables / gantt charts

Hi all,

I am looking to create a template that will allow the following to happen: 3 smaller tables will auto populate a singular master table's headings on both columns and rows.

I am looking to create a really basic gantt chart (calendar view with a list of projects in the rows and the calendar across the columns).

I want the rows (list of projects) to incorporate 2 separate tables (a national list and a local list) that a user can continually add or detract from as and when projects close or open. I would ideally like these 2 lists (national and local) to be in separate tables on a "basic information" tab to avoid any users messing with design of the gantt chart. So when a project is added to either the national table or local table it will automatically update the project list in the gantt chart.

I would also like the calendar of the gantt chart to be split into the months of the year but then split further to reflect another table for team names. So January is sub divided into 3 teams to show the difference for each team in January for example. I would like the sub category of teams to be auto populated from a third table.

The data that I would show in the gantt chart itself will only be a numerical figure against each project for each team in every month.

I am looking to show impact based on a rating for individual teams throughout the year from each project with a total for each team in each month at the bottom.

I can set this up manually but I would like to be able to auto populate the headings of the gantt chart as each person inputting data will have different projects at a local level and also different teams to each other - so if I could have a basic info page for them to create the basic data then I would like for the gantt chart to auto populate so all they have to do is score each project against the teams in each month rather than adjust everything manually when a new project starts or one closes. Is this possible? Any advice would be much appreciated.

Also worth mentioning I am using excel 2016 but the users would be working off of excel 2003 but will upgrade to 2016 in the next year or so.

Thanks in advance!