Sunday, August 7, 2011

yogi_Find And Replace Text Within A Formula

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  

bdburke87 said:
Find and replace text within a formula
I have a block of cells that have formulas referencing another sheet in the same Google Doc. I would like to duplicate these formulas but with a reference to a different sheet in the same Google Doc. These two sheets that are being referenced are identical in every way except this name.
Original Formula Example 1: =Countif(Gettemeier!I3:I39, "FL")
Desired New Formula: =Countif(Ploesser!I3:I39, "FL")
Original Formula Example 2: =ARRAYFORMULA(SUM(IF('Gettemeier'!G45:G77="LS", 'Gettemeier'!B45:B77, 0)))
Desired New Formula 2: =ARRAYFORMULA(SUM(IF('Ploesser'!G45:G77="LS", 'Ploesser'!B45:B77, 0)))
As you can see, I would simply to replace text within each formula, then copy the new formulas into my desired location in the same Google Doc. I have tried SUBSTITUTE, REPLACE, and REGEXREPLACE. I have also tried Showing Formulas and Finding and Replacing. I also copied these formulas to Excel and trying to alter them to be copied back into Google Docs.
Is there a way to Find and Replace text within a formula? Or is there another way to solve this problem?
Well, one way would be to
1) first convert the formula into a text string
2) then use Find and replace command to change Gettemeier to Ploesser
to use the INDIRECT function as illustrated in the following