I’m trying to create a project register in Excel which is to be managed by multiple people. It needs to be useable by multiple people at the same time, all doing different updates simultaneously. I have a document server with locking functionality – ie it locks a document when somebody has it checked out.

My current thinking is that everybody has ‘local’ copies of the spreadsheet. These pull/refresh themselves with the data from the master copy at regular intervals and before any pushing of data to the master copy. Any new data inputted by a user to their local version is then pushed to the master copy. People should be able to edit existing data not just add new data.

I am reasonably proficient with Excel and VBA. I have used ADODB querying of an Access database to do something a bit similar in the past – so if left up to me, I could hack something together but I know it won’t be the best way.

My question is, what is the best/easiest way to implement this from Excel?

Constraints:

• I need to use native Excel 2016 as there a number of key macros already written which I need.
• I don’t want to use the server’s functionality to ‘check out’ the master version. This means that only one person can work with it at once – I need multiple people to be able to input different data and update the master spreadsheet at the same time.