1

How do I create a Master Sheet with formulas that, whenever those formulas are updated, it updates those formulas in sheets made by copying it? So whenever changes are made to the Master Sheet, linked sheets are updated with the same changes in terms of formulas. And if possible structure as well, so if a column is added, that also gets added to all linked sheets.

Very much like how a slide master works. You know, how if you update a header/footer or add a text box/image in a slide master it updates that in every slave slide of that template.

A scenario: A school enters data through a google form. This is then brought into separate sheets for every class. Say I have 15 classes total. These are then updated by the class teachers with things like say attendance. Now if I change one small thing in one of those class sheets, I have to edit all 15 sheets.

Link to sample google sheet: https://docs.google.com/spreadsheets/d/18aPWgfoAcc4NUyvK3hJcFfP6rW2zgFF9NrtgT8NRFjs/edit?usp=sharing

Sometimes Macros work but not always and a new one must be made each time, and then run for each sheet. This is not efficient if you have 63 sheets to update like me.

It feels like one of those things that should already be there! Must be good reasons not to be.

Is there a way to do this either through formulas or through code? I haven't done scripts so would appreciate any help. If possible a method that is data agnostic.

PS: If this can't be done, is there a way to define a macro that works for multiple sheets?

2

1 Answer 1

0

Is there a way to do this either through formulas or through code.

By formula
Maybe. It would depend on the specific formula and how it is structured.

If you want to add a brand new formula, then No.

By code
Yes... probably.
Why "probably"? While there are script methods to make and update variables (such as setValue(value)) and/or formula (such as setFormula(formula), you haven't given any information about your sheets, data, the formula that you use, etc.

So while there is a very high probability that the changes can be made with a script, a more detailed response would need more information that the hypothetical in this question.

8
  • Thanks Tedinoz for taking the time to reply. The reason that I did not provide a sample sheet, is that I would have preferred a method that was data agnostic. It should not matter what the sheet contained. However I spent some time making a redacted version of the school sheet for your perusal. I hope that this helps. Link to google sheet: docs.google.com/spreadsheets/d/… This is only one scenario though. I have another for program with 63 people updating data each with their own sheet.
    – Shard
    Commented Feb 3, 2022 at 5:26
  • A script option is possible. Whether it would be sensible, practical or advisable is another thing altogether.
    – Tedinoz
    Commented Feb 3, 2022 at 7:50
  • Could I possibly use a macro to add some level of efficiency beyond manually updating each change in each sheet?
    – Shard
    Commented Feb 3, 2022 at 12:45
  • Or are there other methods that could be of use that I am not aware of ?
    – Shard
    Commented Feb 3, 2022 at 12:46
  • My understanding is that a macro is a script by another name - there's just a non-technical way of generating the code. But in your case, since you "might" need changes to have a more specific purpose, and in specific places on "many" sheets, I think a script would be far more efficient. Having said that, we're discussing a hypothetical situation so you can try and macro and see what happens.
    – Tedinoz
    Commented Feb 3, 2022 at 22:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.