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]

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: 

1. Store data for each experiment run in a CSV file with field headers on top in a folder organised by experiments.
2. Use Power Query to pull CSV files form the experiment folder and use a function to combine data with different headers. 
3. 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

[#3]

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

[#4]

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

[#5]

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

[#6]

Hi John,

I do not think Excel provides for this ability naturally. I believe, you would have to resort to VBA in this instance.

Regards

Yury 

[#7]

Yes, as soon as new file is created you can't apply to it styles template

[#8]

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.

[#9]

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

[#10]

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

[#11]

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

[#12]

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)

[#13]

Am i correct that submits on Answers and UserVoice? Never tried the button, used both above directly.

[#14]

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 !!

[#15]

Hello Imke

Try this: File -> Feedback

[#16]

Wow - that was a bit too obvious 😉

Thank you Detlef!

[#17]

Odd that the icon is not there for you. It is normally located next to "Share".

[#18]

Hi John,

After years, I am here looking for solution for exact same scenario. Could you help with the solution.

TIA.

[#19]

@VishalRM So strange but just a few days after you I am trying to figure out a solution as well. 

[#20]

@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.