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 ...
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
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,
John
Hi there,
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
Hi Imke,
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)
