Using a sheet as a dynamic master template for other sheets

[John U]

We are running multiple experiments and saving the data for each in a seperate sheet. However these experiments are works in progress and we are continually updating the types of data we capture and the general format of the sheet. It would be ideal to have a master sheet that serves as a template and will update the other sheets without otherwise disturbing their seperate data.

This seems like a reasonably general scenario, so I am hopeful it has a sane solution; but other approaches to achieve the same capability are welcome.

Thanks in advance,

John

[#2]

@knowlgo @VishalRM I may have found something that will work.  So, If ALL of your sheets look the same, you can select them all by holding shift on the tabs at the bottom.  From here you can edit the sheet you are on and the changes will be made to all the sheets selected.  ie, If you add a column it will be added to all the sheets, if you type in a specific cell, the data will show up in the same cell on all the sheets. If you format the cell to a specific color, they will all format to that color.  I'm not sure how far this goes (conditional formatting or things of that nature) but it seems like this may be the answer you are looking for.

[#3]

@knowlgo, @VishalRM, @Trezork, a solution can be using Access and connect it to different worksheets. Worksheets will get updated and work pretty good just the problem is Access would be only local which limits others' contribution and access to the database.
Please let me know if you guys found any other solutions.

[#4]

We have a similar situation at work. Each employee has access to a separate document in a separate sharepoint folder that only they have access to. In this document, the employee enters some information relating to themselves, and other employees should not see this so the documents really need to be separate. So far so good, because there are no problems setting this up.

However, I would also like to be able to add new columns and rows, or perhaps make a change in a formula somewhere in case a bug in the formula is found, and other changes like this, without having to edit and update zillions of separate documents — one for each employee. So, I would like to make a change to a single master template, and have that change appear in all the separate documents (which are of course identical in form) that use this "dynamically updatable master template".

[#5]

@matsandren Your best bet in such a case is to separate logic from data. Otherwise you do end up having to run an update process through all relevant separate files. Perhaps a separate workbook which "connects" to the workbook containing the data. If you ensure that separate logic workbook knows how to fetch (and store) the data, you should be able to place it in a central (read-only) location for everyone to access. The workbook itself contains no data, which is fetched when the file is opened by the user.