How to update multiple excel files?

Question

I have one Master excel file called "00" containing 3 sheets "00", "01", "02". This Master file "00" will be managed by one user. I need to have 2 separate excel files called "01" and "02" that connect to "01" and "02" sheets in master file "00". These separate excel files "01" and "02" will be managed by 2 other users.

what i need now is 1. when 2 users make changes on separated files "01" and "02", it will auto-update sheets "01" and "02" in master file "00".

2. When master user make changes in sheets "01" and "02" within master file "00", it will auto-update separated files "01" and "02"

Auto updates must be both way.

I know there is linked and embedded functions, but looks like it does not support both way update...

Anyone knows how to do it?

Solution

First of all: Excel is not suited to do exactly what you want. It is impossible to have two places where you can view and edit cells that are updated both ways. The key problem is here that the data doesn't have one authentic source and in general, this is a bad design pattern. So I'll give you two alternatives that might come close to what you want.

Embed or link
The best option (as far as I understand your situation) is that you embed or link from the 00 file to the other files? You could create and "Index" worksheet with 01, 02, 03, ... each on a row, with a hyperlink to the original file. Or even and embedded object linked to that file (so you can see the contents of that file).

Share workbook and hide+protect sheet 00
The second-best option might be to have one Excel sheet (00) and make that a shared workbook as suggested. You could make the 00 sheet hidden (or even very hidden; also see this article using the properties of the sheet in VBA) and protect the contents of the file with a password.

Comments

[CallumDA]

It sounds to me like it would be better if you just had a shared workbook - multiple users can be editing the workbook at the same time. (review > share workbook).

[Root Loop]

well, the things is master file is not allowed to share....

[DrMoishe Pippik]

You might be better served by a database application rather than by Excel. The full Office suite comes with Access, though it does not work well in simultaneous use. LibreOffice comes with the free Base application. A database is designed to handle concurrent transactions and to prevent loops, while a spreadsheet is not.

[Root Loop]

Thanks, I know it is better to use DB and that was my suggestion to my boss....but I was told must use excel....there is no more options for me...