Auto change formula in cells when source is changed

[carlo]

Good day experts;

If specific cells have the same formula for example:
C1 = Product( A1;B1)
C16 = Product(A16;B16)
C17= Product(A17;B17)
And so on ...
What formula should i write in C16, C17, ... so that:
when the formula in the source cell, C1, is changed the formulas in C16,
C17, ... would automatically change accordingly.
I tried "=C1" it does not work

Thanks in advance
Carlo

 

[Paul Lautman]

Good day experts;

If specific cells have the same formula for example:
C1 = Product( A1;B1)
C16 = Product(A16;B16)
C17= Product(A17;B17)
And so on ...
What formula should i write in C16, C17, ... so that:
when the formula in the source cell, C1, is changed the formulas in
C16, C17, ... would automatically change accordingly.
I tried "=C1" it does not work

Thanks in advance
Carlo

OFFSET

 

[carlo]

Sorry Paul but what do you mean by offset (my english is not xcellent).
Should i post this question elsewhere? if yes where?

Thanks

 

[Paul Lautman]

Sorry Paul but what do you mean by offset (my english is not
xcellent). Should i post this question elsewhere? if yes where?

Thanks

Instead of using cell references use the OFFSET function with different
references and then refer to a pair of cells for the offsets and another
pair for the widths and depths. Here is an example:
Q1=0
Q2=0
Q3=1
Q4=2
C1 = Product(OFFSET($A$1,Q1,Q2,Q3,Q4))
C16 = Product(OFFSET($A$16,Q1,Q2,Q3,Q4))
C17 = Product(OFFSET($A$17,Q1,Q2,Q3,Q4))
The above should mimic what you have written in your post.
Now suppose you want to change this to get the products of A1-C1 and
likewise A16-C16 and A17-C17.
You simply change Q4 to 3

 

[carlo]

Thank you Paul; i have worked on it over the week-end it worked. However it
is rather too much elaborated: I need time to write the offset function and
time to read it and understand which cell it refers to if i get back to it
for checking. In my case, where many functions in the same document are
involved and where the same formula contains many functions it is rather
stressing and time consuming.

I was wondering if there is an easier way i.e.:
when the variable is a number and not a formula for example if C1= 4,
writing in C16 "=C$1" would solve the problem.

Is there something similarly easy to auto chage functions?

Thanks again

 

[Paul Lautman]

Thank you Paul; i have worked on it over the week-end it worked.
However it is rather too much elaborated: I need time to write the
offset function and time to read it and understand which cell it
refers to if i get back to it for checking. In my case, where many
functions in the same document are involved and where the same
formula contains many functions it is rather stressing and time
consuming.

I was wondering if there is an easier way i.e.:
when the variable is a number and not a formula for example if C1= 4,
writing in C16 "=C$1" would solve the problem.

Is there something similarly easy to auto chage functions?

Thanks again

Take a look at the INDIRECT function