Thursday, December 6, 2012

yogi_Combine Values In Columns A And B Per Specification And Present Result In Column C

                                          Google Spreadsheet   Post  #916
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI    Dec 6, 2012
user dulwek said:(!category-topic/docs/spreadsheets/LBje-w_dXJg)
multiple references to formula
I don't like to paste multiple copies of the same formula, especially when I need to change it.  Is it possible to reference a formula in multiple cells while still keeping the relative addressing?  I can do this in other languages, but I can't figure it out with google sheets.

For example, if I have 200 rows like this:
a1: 1, b1: 2, c1: =a1+a2
a2: "stuff", b2: "stuff", c2: "stuff"
a3: 2, b3: 4, c3: =a1+a2
a4: "stuff", b4: "stuff", c4: "stuff"
a5: 3, b5: 4, c5: =a1+a2
but then I realize the formula a1+a2 is wrong and I need to change it in 100 different places.  Is there a way to change them all at once without copy paste?  It would be much easier if I could figure how to have the cells reference one formula but still keep the relative addressing you get with copy paste.

something like:
z1: "a1+a2", where z1 is some string
and then:
c1: =`z1` where `z1` will be inserted as forumla (not text) and will evaluate to 1+2=3
c3: =`z1` where it will evaluate to 2+4=6
c5: =`z1` where it will evaluate to 3+4=7
and then later if I want to change the formula, I just have to change z1 and everything updates correctly.

I tried unsuccessfully to use arrayformula to copy formula in this sample:

following is a solution to a bit more generalized problem