I am looking to create an employee tracker of sorts on a number of projects that I oversee. I am looking to create a Gantt chart type of tool that will scan through the project sheets and find names of consultants as well as their slotted time on the project. The project sheets have uniform formatting. I have included a screenshot below. I mainly have three questions for this task:

1. How would I create a search function that would scan multiple sheets to find a name.
2. Inside of that search function, when a name is found how would I return the name of the sheet (which is the project name) - the sheets are not formatted currently to have the project name anywhere but the sheet tab. Also potentially need to return multiple sheet names to the same string for proposals as names could be on multiple sheets.
3. What function would I look to utilize to convert the weeks awarded or proposed to the gantt chart? The issue I have here is that I need to filter for just the proposed or awarded columns under each Calendar Week (CW).

Gantt-page-001.jpg

Any and all advice appreciated!