Ask Your Question

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

asked 2015-07-01 07:58:46 +0100

Mountain gravatar image

updated 2020-08-02 14:26:58 +0100

Alex Kemp gravatar image

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 go into each of the 20 other 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 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.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-08-02 14:27:07.131701

2 Answers

Sort by » oldest newest most voted

answered 2015-07-01 08:46:44 +0100

karolus gravatar image

Imaging one Calc-doc with 21 sheets.

in Case you want change something for all sheets: →Right-click on Sheettab →Select all Sheets make your change, and after finish go back to single-Selection-mode by clicking on some other (then actually visible Sheet) Sheettab.

edit flag offensive delete link more


That's a very nice feature and thanks for pointing it out. I selected your answer thinking this would work for me. It did in a simple test. Unfortunately, in my actual situation I have one doc with 40 sheets and I need to change only 20 at a time -- and not touch the other 20. So I need a feature like this that will let me select specific groups of sheets. Does it exist?

Mountain gravatar imageMountain ( 2015-07-02 04:29:39 +0100 )edit

My solution is given in my own answer. I don't have enough points to select my own answer, however.

Mountain gravatar imageMountain ( 2015-09-26 22:47:14 +0100 )edit

answered 2015-07-04 02:35:19 +0100

Mountain gravatar image

Thanks to mariosv I learned that it is possible to select a specific groups of sheets and to then edit cells once and have the changes propagated across all sheets. This is exactly the solution I needed.

To select a specific group of sheets, simply use CTRL-click and click the sheets you want to select. Then make the edits. When finished, click on another sheet tab to remove the selection.

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2015-07-01 07:58:46 +0100

Seen: 653 times

Last updated: Jul 04 '15