Linked formula

[mm0170]

I have a spreadsheet in which I have many cells that contain the same formula across a number of worksheets.

Is there any way that I can have this formula stored in a single place and the cells linked to here so that if I make a change to the formula it automatically gets applied to all the cells that I want it to apply to.

[tek]

Is the formula exactly the same each time it appears, i.e. it has exactly the same references and therefore the same value in, or does it have the same form but refer to different cells?

If it always identical, you could just have the formula somewhere else, and then wherever you want it to appear you just look at that cell.

Can you post an example?

[mm0170]

For example:

In May, I want to take current cumulative figures from January-May (cells B*-F*) for this year and then add the cumulative figures for June-December of last year where * equals the row number.

In June, I will need to take current cumulative figures from January-June (cells B*-G*) and add the cumulative figures for July-December from last year (cells AB*-AG*).

[Mark@Work]

You Might be able to get the effect you want by using the INDIRECT() function.
With this function you can construct a text representation of a cell address or a range.

Thus INDIRECT("A1") returns the value in the cell A1,
and SUM(INDIRECT("A1:A6")) returns the sum of the cells A1 to A6.

What you can now do is set B1="A1:A6" then use SUM(INDIRECT(B1)).
So B1 contains the text representation of the range of cells you want to sum
and the eventual result is the sum of the cells A1 to A6.

If you also included the OFFSET() function it should be possible to construct a formula that will allow all the required ranges to be altered in response to the value of B1.

Thus set B1="A1"
then SUM(OFFSET(INDIRECT(B1),0,0,6,0))

Mark