I’m working with two sheets and need help with a formula problem.
My Setup:
First sheet contains different scenarios, one per row
Each row has formulas that reference various named ranges
Second sheet needs to find a specific scenario ID, get its formula, and change certain text parts
What I Need:
I want to take a formula from the first sheet and replace “_original” with “_updated” in the named range references before using it.
What I’ve Tried:
Manual find and replace works but I need this automated
INDIRECT function doesn’t help since each formula is unique
SUBSTITUTE only works on text, not actual formulas
Example:
Sheet1 has formulas like =SUM(data_original) + AVERAGE(values_original)
Sheet2 should convert this to =SUM(data_updated) + AVERAGE(values_updated)
Is there a way to programmatically modify text within retrieved formulas? The challenge is that each scenario has different formula structures, so I can’t use a fixed pattern.
you could also use formulatxt to get the formula as text, then use substitute on it. like =SUBSTITUTE(FORMULATEXT(A1),"_original","_updated") - but you’d still need to convert it back somehow. not perfect but skips the scripting if that’s what you want.
I’ve been fighting the same formula manipulation problems. henryg’s FORMULATEXT idea is decent but breaks when you try converting back. I found a better combo using FORMULATEXT with INDIRECT if you control where the modified formula lands. Try =INDIRECT(SUBSTITUTE(FORMULATEXT(Sheet1!A1),"_original","_updated")) - but this only works if your modified formula returns a simple value, not when you need the actual formula in the cell. For complex scenarios where you need to preserve the formula itself, you’re stuck with Apps Script. The getFormula and setFormula methods are your best shot since native Sheets functions weren’t built for this kind of formula surgery. You might want to restructure your named ranges with a parameter system instead of hardcoded suffixes - could save you this whole headache.
You’ll need Google Apps Script since Sheets can’t change formula text on its own. Had the same problem building a scenario comparison tool last year. Here’s what worked: Use getFormula() to grab the formula as text, do a string replacement, then put it back with setFormula(). Loop through your source range, get each formula with range.getFormula(), use JavaScript’s replace() to swap “_original” with “_updated”, then write the new formula to your target sheet. The beauty of replace() is it works on the whole formula string no matter how complex it gets. You can even throw in regex if you need fancy pattern matching. Works with any formula structure since you’re just doing text manipulation before Sheets reads it.