Excel – How to Update Multiple Excel Files

microsoft excelmicrosoft-excel-2013

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

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?

Best Answer

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.

Related Solutions

Excel – Auto: enable automotic update of links Ms-Excel

Check out this KB article method 4:

Method 4:

Enable the automatic update for data connections or workbook links in the Trust Center To enable the automatic update for data connections or workbook links in the External Content section of the Trust Center, follow these steps:
Click the Microsoft Office Button, and then click Excel Options.
Click Trust Center.
Click Trust Center Settings.
Click External Content.
Click to select the following options, as appropriate for your situation:
    Prompt user about Data Connections
    Prompt user on automatic update for Workbook Links
Click OK to close the Trust Center dialog box.
Click OK to close the Excel Options dialog box.

You cannot and should not have objects or processes managed by the trust center automatically do anything. This is setting a bad precedent. Instead, you should set the workbooks to prompt the users to update every time they open the workbook. If the users are prompted, they will be reminded to update the links.

Additionally, you can take a look at this KB article

Excel – How to automatically update values referenced from one sheet to another in excel

You can reference a worksheet within the same file using <SHEETNAME>! e.g. =Sales!B1 where 'Sales' is the worksheet name as defined at the bottom of your Excel window

Best Answer

Related Solutions

Excel – Auto: enable automotic update of links Ms-Excel

Excel – How to automatically update values referenced from one sheet to another in excel

Related Question