Apply formatting and formulas to another sheet?

Question

I have a template tab that contains all formulas and formatting. Then one tab for each user. I know how to reference cell content with =template!A1. How to reference formula that reference current tab? And how to reference formatting?

Link to my sheet

Answer 1

Spreadsheet functions cannot access either formulas or formatting of other cells. (Script functions can, but that is another story).

It seems what you want to do is the duplicate your template sheet, so that new sheets will have the same formatting and formulas. There is an easy way to do this: "Duplicate" command in the context menu of the sheet.

This copies all values, formulas, and formatting.

However, if you think that the values in column B may be changed in the future, then you don't want them to be just copied to other sheets: you want references. To achieve this,

1. Duplicate "template" once, the new copy could be "user sheet". Edit the cells in B column of "user sheet" so they reference the template: =template!B5, and so on.
2. After that, duplicate only "user sheet", not the template sheet. The duplicates will have the same references =template!B5 in them. The "template" sheet could even be hidden, because you'll need it only on the occasion when the values need to be edited.

Aside on protection

Instead of telling users "only edit yellow cells", you can actually prevent them from editing other cells, by using sheet protection with exception of a given range:

However, protection does not get copied by the Duplicate command, so you would need to either add it manually to every sheet, or use a script for copying protections/permissions.