The Excel Nerd’s Reply:
The problem as you described it isn’t that hard. Or, I should say, the problem as defined by the boss isn’t that hard. The reason the other two guys (and now, me) say it will be a lot of work is because we all know how quickly data can become mangled or irrelevant when it is stored in two separate places.
Example so the boss can understand the complexity: Say that two Employees both update their records at the same time. EmpB pushes save on his document: your code triggers to upload his commentary, or percent complete, or whatever is being tracked. The code tries to fire. What the code doesn’t know is that EmpA pushed save a second earlier on a different computer, and his copy of the code already has the boss’s file open for syncing. Best case scenario: EmpB’s code throws an error. Worst case: They both somehow gain write access to the boss’s file, and write over each other (unlikely for technical reasons, but a great way to describe the problem-slightly more likely is that the boss’s file becomes corrupted, and is lost). Now, your code for copying lines needs some way to know it failed, and check the records in both copies of the document to compare with one another. That throws more complications. It escalates from there… imagine if the boss’s code was trying to push something out at the same time one or more employees were trying to push something back!
CeeBee brought up deleting records, changing records, and finding duplicates (say that somehow the code fires twice). The biggest problem was indirectly pointed out by CeeBee… Security. This opens files up to all sorts of tampering, whether deliberate or accidental. As a nerd, I’d say “no way” to this.
If he insists on using Excel, your best bet is to have him have to open a specific employee’s spreadsheet and add the comment there. Maybe put shortcuts to everyone else’s spreadsheets in a folder he can access. You’ll still need to open up some security bits for him to be able to open other’s files. You can write some code to go and pull all those employee’s spreadsheets into one master for when he wants to review, but it will still be clunky. You’ll have to work out some error checking for when an employee has his file open for editing (Excel gets picky about that).
Other options: Are you using Outlook? If so, what about using the “Assign Task” feature? It is short on options, but I’m not clear on what he wants other than being able to assign tasks and see the progress. Do you have Microsoft Access in your Office suite? You’d have to learn how to make a simple database (not very complicated), and a savvy user might still be able to get into other people’s records, but the synchronization of data would be instant. Given that he wants a homebrew option, Access would likely be perfect. (I notice 74AD pointed that out (database) while I was writing this, as well as the idea of having the boss’s file pull everyone else’s.)
I’m afraid that’s all the time I can put into this right now. I’d say Access, or some open source option is your best bet. I’ve not researched it, and cannot recommend any specific software, sorry.