Yogi Anand, D.Eng, P.E. Google Spreadsheet www.energyefficientbuild.com
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
or
to use the INDIRECT function as illustrated in the following
Love the blog, Yogi. You've been so much help!
ReplyDeleteI have a follow-up question, though, regarding your tip to "convert the formula into a text string then use Find and replace command..." I recently took this advice, however, when I attempted to return the text strings into formulas (i.e. remove the character added in front of the = sign), each respective cell was relegated to "#ERROR!" with a red border ("parse error" to be exact).
On further inspection, I discovered that, by tweaking an inconsequential part of the formula in each cell (e.g. removing an unnecessary space) and pressing the "enter" key, the formulas would be restored to proper working order. However, this menial task is extremely tedious for 100-plus cells.
Any idea (a) why this might happen or (b) how one could efficiently solve the issue? For example, here is a spreadsheet with similar problems: https://docs.google.com/spreadsheet/ccc?key=0AjepGwM_s_fEdHVVaXJ4TW5uNnpBWm1KMmtnekVlWmc