Clone formula to other cells

[umberto79]

Hello everyone,

I have such a task and I do not know how to do it.

I have a formula in cell B1.The formula is A1 + A2.
I am looking for a method that I can "clone" formula from a parent cell(B1) to other cells.

The whole thing is that B1 is like a parent formula and if I change in B1 to e.g. A1 + A3, It will change automatically G1 to formula F1 + F3 and get the data from F1 and F3.

I know how to do it in VBA, but in this workbook I have to avoid it.

Have any of you met with the same and can share your knowledge?
Thanks in advance

[6StringJazzer]

One way to do this is, instead of thinking about it as cloning a formula, put parameters someplace that all formulas will refer to.

For example, if you want B1 to start as =A1+A2, then use H1, H2, I1, I2 to store displacement values. That is, A1 is 0 rows and 1 column from B1, so in H1 put 0 and H2 put 1. Same for A1. Then in B1 your formula is:

=OFFSET(B1,$H$1,$H$2)+OFFSET(B1,$I$1,$I$2)

Then you can copy and paste that formula to G1. Then when you change H1:I2, the results of both formulas will change.

[Greg M]

Hi there,

In my opinion, the most appropriate approach here is to use a defined name formula.

It's not easy to explain a defined name formula in a few words here, but you should be able to find the information you need in links such as the following one:

https://www.ozgrid.com/Excel/named-formulas.htm

Hope this helps,

Greg M

[Greg M]

Hi again,

The attached workbook contains an example of a simple worksheet-level named range formula (forSumTwoCells)

At present the formula is defined as

Please Login or Register  to view this content.

and cells B6, B11 and B16 behave as if this formula had been copied there from cell B1.

If the named range formula is amended to

Please Login or Register  to view this content.

then cells B6, B11 and B16 will be updated to reflect this.

A VERY important point to note is that because the named range formula was defined when cell B1 was the active cell, the formula can be edited (meaningfully!) only when the active cell is B1.


Hope this helps,

Regards,

Greg M

[Greg M]

Hi again,

Many thanks for the Reputation increase - much appreciated!

Best regards,

Greg M