0

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".

  1. 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?

Root Loop
  • 1,015

1 Answers1

2

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.

agtoever
  • 6,402