Auto change formula in cells when source is changed

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

carlo - 13 Jun 2006 19:06 GMT

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

Reply to this Message

Paul Lautman - 13 Jun 2006 19:26 GMT

> Good day experts;
>
[quoted text clipped - 10 lines]
> Thanks in advance
> Carlo

OFFSET

Reply to this Message

carlo - 14 Jun 2006 07:33 GMT

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

Thanks

> > Good day experts;
> >
[quoted text clipped - 12 lines]
>
> OFFSET

Reply to this Message

Paul Lautman - 14 Jun 2006 08:40 GMT

> Sorry Paul but what do you mean by offset (my english is not
> xcellent). Should i post this question elsewhere? if yes where?
[quoted text clipped - 17 lines]
>>
>> OFFSET

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

Reply to this Message

carlo - 19 Jun 2006 10:17 GMT

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

> > Sorry Paul but what do you mean by offset (my english is not
> > xcellent). Should i post this question elsewhere? if yes where?
[quoted text clipped - 32 lines]
> likewise A16-C16 and A17-C17.
> You simply change Q4 to 3

Reply to this Message

Paul Lautman - 30 Jun 2006 17:13 GMT

> 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
[quoted text clipped - 49 lines]
>> likewise A16-C16 and A17-C17.
>> You simply change Q4 to 3

Take a look at the INDIRECT function

Reply to this Message