Simultaneous Update

Hey guys,

I am sure many of you would have faced such questions as i have and this led me to think and think...so i started this thread.

I have seen several OPs asking for questions requiring simultaneous update between files .for e.g. I want this master file to be updated as soon as there is an entry n the team member's file, if i change the dept name in this row, the data should be removed from the old dept tab and copied to the new dept tab, etc. What do you think is the best way to do this?

I thought of the following options -

1. On a worksheet event, clear the destination sheet completely and reload with the required data.
2. On update, check if the row already exists. If not, copy it in the next available row. But how do i check? Keep running a "find" query? If not found, then update?

Please advise. I really want to learn something out of this.

I just steer clear of these. Keeping multiple workbooks synchronised has just got problems written all over it. If anyone at work asked me how to do this with Excel I'd make them sit in the corner for an hour and think about how silly they were being.

Dom

I've been trying to ween my coworker off of multiple workbook linking and merging. The way I see it, if someone wants to collate data like described, then a data base (a real one) should be used and not multiple workbooks located in multiple folders. Plus, the whole notion of updating one workbook from the updates of other workbooks makes my head spin and the only time I like my head spinning is when there are a few beers in my belly.

I usually find such requests when the team updates their individual workbooks and the team leader wants the summary. But they want it live !!!

Wouldn't cell linking be the best way for a summary in a team leader's workbook?

Mostly they dont want all the columns. Just a few. This complicates stuff.

I can give examples of open threads with such requests.

Tell them to use a database.

Originally Posted by OnErrorGoto0

Tell them to use a database.

You mean Access?

Not necessarily Access but this is definitely a job for a database. It's what they are designed for.

They can also share the file and the team lead/manager can access the same file. Have one tab for each employee, right?

Never ever ever ever ever ever ever ever ever ever use Shared Workbooks. Unless your life is boring and needs livening up.

Other than Access, what other databases in MS Office can they use?

None, since there is only one database program in MS Office.

Thats what i thought. If they dont know Access, they will have to choose other options....not provided by Office.

Yes - there are plenty of free ones like SQL Server Express.

In many cases this will do in Excel:

If all users can only open a copy of a central workbook : Worbooks.add "\\central.xls"

You only have to provide for a small macro to save the modifications made by each user in the copyfile into the common workbook "\\central.xls"
The opening of the common file, the adaptation with the modifications and the saving of the common file will only last milliseconds if performed by a macro.
The chance of multiuser interference will be minimal.

Especially if you allow users only to use userforms to change anything you can have a very standardized structure of the data in Excel.
Everyone who has read-only access to the common file will also only be provided with a copy of it.

There are a lot of organizations that have no trillion employees working with the same data simultaneously.

If you can't use a database to query employee information and you must use workbooks, make sure all workbooks are saved in the same shared folder and then write a procedure to extract from each workbook the pertinent data and place it into the viewable workbook for your manager. I would personally dread this type of job but if you format all of the employee workbooks in a consistent manner then the extraction process will only be half as horrible to code.