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 ...
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
I would suggest to re-think the layout entirely. Why not have all data on one sheet, with an additional column which would probably contain something like the sheetname you currently have for each sheet of data. This makes it very easy to do reporting using e.g. pivottables.
Jan,
We are trying to keep each researcher's responsibities confined to their own sheet. Mixing everything on one page would defeat that. We would also still have to duplicate each new additional change across the sheet unless we collapsed everthing in some unatural way.
Best,
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
Yury,
I think you have my request backward. I want to update the tables, not the data. For example, say I had the world's simplest table: a column labeled Name next to one labeled Address. And say this was used for 4 different sheets, each belonging to a different sales person using it for contacts.
Then one day we decided we needed to add in phone numbers. I would ideally like to update the master sheet with a third Phone column (empty of data) and have it appear on the other sheets (also empty of data). The maintainers of those other sheets could fill in the new data whenever they got it for their own contacts.
If this were really our full application we would use a database. However our real application has realationships between the cells that call for a spreadsheet.
We could of course do this manually without involving any external files or VBA scripts. The question is whether Excel provides for the ability to dynamically mirror tables.
Best,
John
