Need advice: syncing multiple shared workbooks with a centralized workbook

I am about to embark on the task of integrating spreadsheets that will have data shared by several people. We have salespeople, project managers, engineers, the company owner, and others, who wish to view and/or edit info related to a couple hundred or so active projects. For example, the user needs to be able to change or add a due date to a milestone, and upon saving or syncing that change needs to be made to the other users' workbooks.

I have been tinkering with VBA for a little over ten years now, and thanks to John Walkenbach and this forum I have some pretty slick spreadsheets, but other than using VLOOKUP for pulling in data from a product part number and price list, I have not manipulated external data at all. I could probably write a very clunky code that would take a half an hour to run, but before I get started I need someone to point me in the right direction for making this happen, please.

Obviously, there's a risk that two people will be working on the same project, and one or both of them may be working offline at the time. When the two users save or sync, and the data is updated in the master spreadsheet, if a change has been made to a cell by both of them there's an error message and options for resolving the conflict are available. (But perhaps I need to just prevent that by users having ownership of a project record, locking out others from editing rights.)

Each project will be contained in rows, and the data categorized in columns. The columns have a range name and the row could too, or I'll work off a project number to allow for identifying a record that is present in multiple workbooks. I have considered using the Intersect function (using the column name and project number) to then compare what's in a given cell for a particular record on both the master spreadsheet and the other spreadsheets that have this record.

One problem I foresee is the ability to determine when data is newer and needs to be updated. I was thinking about using the Comments property to store a date and time of when data was changed, but I would like to use this for other purposes. I haven't experimented with this, but I was curious if I could use the ID property to store this 'modified' date for individual cells.

But, like I said, just in case this is a terrible approach and/or my spreadsheet will be super slow, I could use some advice on the fundamentals of a better way to do this.

Thanks!

I'd be awfully tempted to see what google docs has to offer - I think this would be much better suited to doing what you want

Perhaps. But I know this can be done with Excel and VBA programming. I just need some advice on the best way to go about it. As I said, I think I could do this in a very slow and clunky way. There has to be someone that has done something like this before, so I want to hear how they did it.

It can indeed be done with excel and vba, but so can a lot of things. The problem is that you're fighting an uphill battle, excel just isn't fundamentally designed to do this so you'd have to write the while framework to make it work from scratch. For starters, you'd need some sort of internal logging in each spreadsheet to record changes, each of which would need timestamping, when you then consolidate data you'd need to know which the most recent changes were otherwise you'd end up with stale, or worse incorrect data.

These types of projects are normally done using a database since it's much easier to track that sort of stuff in one rather than trying to Hack it in a spreadsheet.

I'd look for a platform that handles multiple, possibly concurrent users, that allows collaboration and real time changes. It saved a massive amount of work, not to mention headaches.

Have a look at this:http://support.google.com/drive/bin/...answer=2494891