Best practices for replicating changes in one workbook in another workbook?

Hi there,

It's late here so I don't have time to go into any details.

The approach you should use is to have separate DATA workbooks for each of your Users, and a common INTERFACE workbook into which each User can retrieve the data values from his/her DATA workbook. DATA workbooks should be absolutely "vanilla" and should contain nothing other than data values. These workbooks will never be accessed directly by Users, but only via the INTERFACE workbook.

The INTERFACE workbook contains all of the required formulas, formatting, layouts etc.

Using this approach means that any necessary changes/improvements are made only to a single INTERFACE workbook.

Hope this helps to get you moving in the right direction.

Regards,

Greg M

Thanks for your suggestion Greg.

I'm afraid, however, that a vanilla data sheet won't work because of the conditional formatting needed to be able to indicate to the user which cells require data entry and to make invisible certain cells based on the entered data.

Any other thoughts? Does it sound like macros should work but I'm just not doing it right?

Thanks in advance

Hi again,

I think you've missed the point!

A DATA workbook contains only data values - nothing else! No conditional formatting, no fancy fonts, no borders, no background colours, no formulas, no macros, no hidden columns etc., etc., etc.

All of the above "nice bits" are accommodated in the single INTERFACE workbook.

If I have understood correctly, the several workbooks you're talking about synchronising are all identical, apart from the input data provided by the Users. From one of these typical workbooks, it's a relatively simple step to create a single INTERFACE workbook into which you "plug" the raw data taken from the DATA workbooks of your various Users.

Hope this clarifies the situation a little.

Regards,

Greg M

Hi Greg,
I think I might still be missing the point or I'm not explaining myself clearly enough about my needs and limitations.

The data sheet template can't be devoid of formatting because it is required in order to direct the user as to which cells require data and which kind of data. The attached sample shows a data sheet "D3..." and a results worksheet "R10...". The values in Column F are those entered by a user. Gold colour indicates that primary data is required, green indicates that assumptions can be used. There are other colours but this is just a small example. The results sheets grabs the data entered by the user in all of the "D" sheets and calculates the values. No user interaction is needed for any of the "R" sheets.

You are correct that I need for the structure of all the "D" and "R" sheets to be the same across all versions of this tool and only the values entered by each new user in the gold and green cells should be different. I'm looking to make this happen without having to replicate any change I make in the master version in all the country-specific versions I am working with.

I'm not sure I get what you mean by the INTERFACE workbook. Is there a link you should share? I did a search for "interface workbook" and didn't come up with anything relevant.

Thanks again for any further support!

Hi again,

Ok, thanks for that example - it makes the picture a bit clearer.

To illustrate the point I'm making, let's consider just the "D3 Epid. & Service Data" worksheet.

As far as I can tell, Users enter data only in the non-formula cells in Column F, and they're also free to use the various filters you provide - everything else on the worksheet is more or less "set in stone".

Now consider a situation where your INTERFACE workbook contains a "D3 Epid. & Service Data" with NO data values entered in Column F (the formula cells should remain).

Then consider four (as required) separate workbooks which contain ONLY the (non-formula) values which up to now were stored in Column F. The cells containing those values don't need to be formatted in any particular way.

Now comes the interesting bit! When the User opens the INTERFACE workbook (s)he is asked which DATA workbook should be used on this occasion. When the DATA workbook has been specified, it is opened, its data are read, "plugged into" the INTERFACE workbook, and the DATA workbook is then closed.

When the INTERFACE workbook is closed, the process happens in reverse - the appropriate DATA workbook is opened, data are read from the INTERFACE workbook, "plugged into" the DATA workbook, and the DATA workbook is then closed.

Implementing the above isn't a five-minute job, but it's probably not as complicated as it sounds, and it's definitely the way to go if you want to avoid the heartache of trying to synchronise changes in multiple workbooks. There seem to be only four workbooks right now, but this time next year there might be forty-four!

I hope that I've at least explained the concept behind what I described previously.

Regards,

Greg M

Hi again,

Many thanks for all of your feedback and also for the Reputation increase - much appreciated!

Hope you found some of my suggestions helpful or interesting.

Best regards,

Greg M