Formulas to automatically update on multiple sheets

**tone640** · 06-15-2011, 11:46 AM

I have been searching for hours for a solution to this issue, and I'm sure that there's a simple way round this.

I need one sheet where you can edit your formula, and for multiple identical sheets to automatically update with the changes to the formula.

In the same way =Sunday!A1 on Monday's sheet will update the value if I change the value in A1 on the sheet called Sunday, I need the formula to be updated if I were to change it.

I can workaround by grouping the sheets and copy & pasting, but it would be simpler and more reliable to only change one copy.

The closest I have got is to define named ranges with the formulae in the 'Refers To' field, but this means that the formulas are stored as named ranges, not in cells.

Thanks in advance.

**tone640** · 06-16-2011, 05:58 AM

I have attached a sample workbook. The formula in cell A1 sums B1 and C1 on each sheet. If I change this formula to also sum D1 on Sunday's sheet, how can I make it automatically update on Monday's and Tuesday's?

**TMS** · 06-16-2011, 06:15 AM

Does this do what you want:

=SUM(OFFSET(B1,0,0,,COUNTA(B1:Z1)))

Regards

**outofthehat** · 06-16-2011, 06:18 AM

You can create/edit identical formulae on multiple sheets by selecting the sheets before you edit. So, select the sheet1 tab, shift-click on sheet3 tab, and you have all 3 selected (or ctrl-click to select individual sheets one at a time). The tabs of the selected sheets go white.

Now, any changes you make, including edit formulae, entering text or formatting, will apply to all selected sheets.

**tone640** · 06-16-2011, 07:58 AM

Originally Posted by TMShucks

Does this do what you want:

=SUM(OFFSET(B1,0,0,,COUNTA(B1:Z1)))

Regards

TMShucks, I don't think that I could use this as described in my original post, but thanks for having a look at it for me.

Originally Posted by outofthehat

You can create/edit identical formulae on multiple sheets by selecting the sheets before you edit.

outofthehat, thanks for your suggestion. I had hoped to avoid this solution, but I may have to accept that it can't be done without VBA.

**outofthehat** · 06-16-2011, 09:11 AM

It very much depends on the calculations you're trying to achieve. There are plenty of ways to ensure that your formulae automatically expand to include additional data, such as the OFFSET/COUNTA combination suggested by TMShucks - these are very desirable if you're planning to add data in on a regular basis. There is (almost) always a better way than having to manually edit the formula!

**tone640** · 06-16-2011, 09:57 AM

The report I am creating will continually change until it is complete so I am having to change formulae repeatedly.

The example I gave was as simplified as possible. OFFSET accommodated additional columns, but it did not change the formula across multiple sheets, which is the real issue that I am looking to solve (without grouping sheets).

Here is a formula that I have had to manually edit:

=IF(ISERROR(HLOOKUP($B$8,cont,ROW(B2),FALSE)=0),"",IF(HLOOKUP($B$8,cont,ROW(B2),FALSE)=0,"",HLOOKUP($B$8,cont,ROW(B2),FALSE)))

To start adding OFFSET as well would make it even more difficult for me to decipher the formulae!