Google-sheets – Apply formatting and formulas to another sheet

google sheets

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

Best Answer

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.

duplicate

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,

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

protect

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.

Method 1: match

Use custom formula

=iserror(match(B2, Sheet1!$A$1:$A$6, 0))

for data validation.

B2 is the upper left corner of the range to which the rule is applied.
The third parameter 0 requires exact match
The formula returns True only if match returns an error, which means no match is found.

Data validation accepts the values for which the formula evaluates to True. Same can be done with conditional formatting.

Method 2: regexmatch

Form a regular expression from the list and validate against that. The string

 "^(" & join("|", filter(Sheet1!$A1:$A, len(Sheet1!$A1:$A))) & ")$"

concatenates all values in that column into a regular expression that will match any of them. (For example, "^(first|second|third)$").

Then you can use regexmatch by this string to validate or conditionally format the data anywhere else. For example:

=not(regexmatch(B2, "^("&join("|", filter(Sheet1!$A1:$A, len(Sheet1!$A1:$A)))&")$"))

which means the content must not match the regular expression.

Google-sheets – How to link cells from one sheet to another sheet and keep formatting and comments

Comments

Comments cannot be copied. They are not considered part of the contents of a spreadsheet, they are more like chat messages, the record of a conversation that took place about that spreadsheet. If there was no such conversation about a copy of that spreadsheet, comments won't be there.

Alternative: use notes instead of comments. They get copied same way as cell contents.

Formatting

In the situation you have, it'd be better to create a blank template of grade report, with all the formatting, and then copy that to individual students. importrange is meant for importing data, not formatting. (Also, it does not import notes or comments.)