Jun 20 2017 12:36 AM
Jun 20 2017 12:36 AM
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,
Jun 20 2017 06:19 PM
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:
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
Jun 20 2017 07:57 PM
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...
Jun 20 2017 09:08 PM
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.
Jun 20 2017 10:54 PM
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.
Jun 20 2017 11:19 PM
I do not think Excel provides for this ability naturally. I believe, you would have to resort to VBA in this instance.
Jun 21 2017 02:26 AM
Yes, as soon as new file is created you can't apply to it styles template
Jun 21 2017 02:32 AM
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.
Jun 21 2017 01:34 PM
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.
Jul 01 2017 04:16 PM
As there has been a lot of interest in this topic, I suppose the polite thing to do would be to summarize with the "non-answer":
It has been confirmed that there is no way to do this in Excel. The likely alternatives are to use a lot of VBA, or to move to a database. I will be doing the second.
Thanks for all of your replies,
Jul 02 2017 02:56 AM
I've used self-referencing table from PowerQuery for tasks like this before: Just merge/append the current data with the empty master table will add all new columns from the master table and also maintain content that's been entered into the (previously) new columns as well.
But there seems to be a bug in my current Excel-version for it (O365 current channel). I haven't found a section for bug-reports in Excel yet: Is there such a place?
Thx - Imke
Jul 03 2017 05:52 AM
There is always the send-a-smile button in Excel (top-right corner of the app) to send bugs to MSFT (or anything else you don't - or do- like for that matter)
Jul 03 2017 06:05 AM
Am i correct that submits on Answers and UserVoice? Never tried the button, used both above directly.
Jul 03 2017 10:35 PM
Thank you Jan Karel!
Please bear with me, but I cannot find this button in my Excel:
Do I miss sth here or might this not be availiable in all Excel-versions? (I still have the smiley in the PowerQuery-Part, but would prefer to report that into the correct chanel initially).
Thx again !!
Jul 03 2017 10:47 PM - edited Jul 03 2017 10:48 PM
Try this: File -> Feedback
Jul 07 2017 06:14 AM
Mar 11 2021 04:37 AM
Mar 24 2021 02:07 PM
@VishalRM So strange but just a few days after you I am trying to figure out a solution as well.
Mar 25 2021 09:05 AM
@knowlgo @VishalRM it looks like he responded a few areas up that there isnt an ability to do this in excel. It's unfortunate because I am looking for the same exact thing as well! ironic how so many people need it at the same time. I'm gonna keep looking and if I find anything I'll post here again.