Forum Discussion
Using a sheet as a dynamic master template for other sheets
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
Hi John,
when you are talking about adding new types of data, do you mean adding new data field into an existing experiment database, retaining the historical data, and starting to populate the new field from the moment it was added? If this is the case, you could do the following:
- Store data for each experiment run in a CSV file with field headers on top in a folder organised by experiments.
- Use Power Query to pull CSV files form the experiment folder and use a function to combine data with different headers.
- Have a list of field headers which you would use in the above-mentioned function.
I have attached example files of how it may work. Please save the CSV files in a folder and update folder path in the Reference tab. You need to have Power Query installed for the functionality to work.
Hope this helps
Yury
Yury,
I think you do understand my objective, and I thank you for your suggestion. However I am hoping to avoid having to keep seperate files and whatnot or I would probably just resort to a database solution. I have to believe there is a simple way to just achive this amongst the sheets...
John
Hi John,
you would need to use VBA. If you dumped a new set of data into a template table, the code would have to synchronise the fields between the tempalte and destiation table, and then append the new data to the old one in the destination table.
Thanks
Yury
- Hi John,
After years, I am here looking for solution for exact same scenario. Could you help with the solution.
TIA. - 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".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.
