automatically updating a master list workbook from a template (multiple wookbooks)

We are in the construction business and use a very basic excel spreadsheet to keep important information about each job we have. We use an excel workbook as well as a series of folders to keep pdf's, contracts etc.

Within the workbook we keep information on changes made to each job including a summary of the change, date created, cost and change number (some jobs can have 20 or more changes). This information is entered into the job workbook by project managers and is also entered into a master change order list that is used as a reference by the accounting department. Currently this is a double entry process (the sheets are similar so some info can be copy/pasted) and I am looking for a way to enter the information once in the job workbook and automatically create a new entry in the master change list with the job name and number as well as the information about the change.

I can create a reference easily enough but the trouble I run into is renaming the workbook template for each job and how it references to the master change workbook.

Any advice would be greatly appreciated.


Thanks!!