Forum Discussion

John U's avatar
John U
Copper Contributor
Jun 20, 2017

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

 

  • matsandren's avatar
    matsandren
    Copper Contributor
    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".
    • JKPieterse's avatar
      JKPieterse
      Silver Contributor

      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.

  • VishalRM's avatar
    VishalRM
    Copper Contributor
    Hi John,

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

    TIA.
    • knowlgo's avatar
      knowlgo
      Copper Contributor

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

      • Trezork's avatar
        Trezork
        Copper Contributor

        knowlgo VishalRM I may have found something that will work.  So, If ALL of your sheets look the same, you can select them all by holding shift on the tabs at the bottom.  From here you can edit the sheet you are on and the changes will be made to all the sheets selected.  ie, If you add a column it will be added to all the sheets, if you type in a specific cell, the data will show up in the same cell on all the sheets. If you format the cell to a specific color, they will all format to that color.  I'm not sure how far this goes (conditional formatting or things of that nature) but it seems like this may be the answer you are looking for.

  • Yury Tokarev's avatar
    Yury Tokarev
    Steel Contributor

    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

    • John U's avatar
      John U
      Copper Contributor

      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

       

      • JKPieterse's avatar
        JKPieterse
        Silver Contributor

        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.

Resources