Issues with links to Data Sources between Workbooks and Possible Excel Application Develop

Hello all,

First off, let me preface my question by saying that from what I can gather, this would be a massive undertaking. I'm trying to revolutionize the way one of my company's departments enters/archives/interacts with the data they gather. The other problem is that due to security and anti-fraud regulations, I cannot post any examples from the existing work.

Right now, they are using individual files that have a template sheet which contains macros to start a new sheet (based on a new day) and macros to paste one of 5 different templates (approximately 20 rows X 8 columns of a 'form') for data collection in the prior sheet which was created with the prior macro. Once the template 'form' is pasted in to the prior sheet, they enter data they gather into it. This includes typing into fields and using dropdown lists. Once they've finished the form, they go back into the template sheet and paste another template into the original worksheet and start the process over again.

The reason for re-imagining this process is that they often need to copy a form from another employee's spreadsheet and pasting it into their current days' worksheet. This is always done with the intent to edit the information based on new information (including changing dropdown list selections). Within each employees own spreadsheet, the references that their dropdown lists use are named using that employee's initials. This has caused many issues due to the fact that the workbooks now contain links to other data sources. I can't see any easy way to solve this issue, which leads me to believe that creating an Excel Application might be a better choice.

I'm not completely sure that I'm headed in the right direction but what I'm totally assured of is that my current knowledge is inferior to that which is required to accomplish either fixing the current process or creating a new one.

I appreciate any and all the help I can get. Below is a mock-up of the structure of the template that each employee (only 7) has. The only difference is that it lacks employee initials in the defined names of the dropdown list items.

Thanks,
Orestees