Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

How to: a live template sheet that is used by multiple sheets

I want to make one "template" sheet that contains all the required headings, formulas, formatting, etc. I want to have 20 other sheets mimic this template sheet. Data will do into each of the 20 other sheets.

For example, say my template sheet contains, in cell C1, the formula =A1+B1. I would like the 20 other sheets to contain, in their own cell C1, this formula. And it should reference the cells A1 and B1 in the local sheet. Each of those 20 other sheets will have different valus in cells A1 and B1. So the sum in C1 will be unique to each sheet, but each sheet will not have its own formula.

If I update the formula in the template sheet to be =A1*B1, each of the other 20 sheets should immediately reflect the new formula and their own cell C1 will display the new result, operating on the existing local data in each sheet.

In short, I want to be able to update a formula only in the template sheet and have all other 20 sheets immediately use this new formulas on their own data.

I understand that there will be limitations. For example, the structure of the data areas in each of sheets must not change, etc.

My goal is to avoid replication of formulas and to avoid having to change things 20 times when I make one change to the formulas used in all 20 sheets.

How to: a live template sheet that is used by multiple sheets

I want to make one "template" sheet that contains all the required headings, formulas, formatting, etc. I want to have 20 other sheets mimic this template sheet. Data will do go into each of the 20 other sheets. sheets independently.

For example, say my template sheet contains, in cell C1, the formula =A1+B1. I would like the 20 other sheets to contain, in their own cell C1, this formula. (I don't want to write the formula 20 times.) And it the formula should reference the cells A1 and B1 in the local sheet. Each of those 20 other sheets will have different valus in cells A1 and B1. So the sum in C1 will be unique to each sheet, but each sheet will not have its own private formula.

If I update the formula in the template sheet to be =A1*B1, each of the other 20 sheets should immediately reflect the new formula and their own cell C1 will display the new result, operating on the existing local data in each sheet.

In short, I want to be able to update a formula only in the template sheet and have all other 20 sheets immediately use this new formulas on their own data.

I understand that there will be limitations. For example, the structure of the data areas in each of sheets must not change, etc.

My goal is to avoid replication of formulas and to avoid having to change things 20 times when I make one change to the formulas used in all 20 sheets.