I’m working with two sheets where I need to dynamically modify formulas. The first sheet contains different scenarios in each row, and every row has a unique formula with references to named ranges. The second sheet needs to find a specific scenario ID, get its formula, and change certain text within those named range references.
I have multiple rows where each contains formulas like =SUM(DataSet_v1) + AVERAGE(Values_v1) but I need to convert them to use _v2 instead of _v1 in the named ranges. Each formula is different so I can’t use a simple INDIRECT approach.
I tried using SUBSTITUTE but it only returns text and doesn’t work with formulas. Is there a way to programmatically replace text within formula references and have the result still function as a working formula? The challenge is that I need this to happen automatically when looking up different scenario IDs.
For example, if I look up scenario 1, I want to get its formula =SUM(Budget_old)*0.5 and automatically convert it to =SUM(Budget_new)*0.5 where only the named range reference changes.
Been wrestling with this for years. Google Sheets lacks robust formula manipulation capabilities; you can’t programmatically modify formula text and keep it functional with just built-in functions. The getFormula() and setFormula() method works well, but I also utilize getRange().getA1Notation() for handling complex cases involving cell references and named ranges. Be cautious, as referencing non-existent cells in the target will lead to errors, even if the text replacement succeeds. I recommend using a helper column to test converted formulas before replacing the originals, especially with various scenario lookups.
Hit the same issue last year with financial model versioning. Google Sheets formulas can’t handle dynamic text replacement like this - SUBSTITUTE just turns everything into text strings. I ended up writing a custom Apps Script function that grabs the formula from the source cell, does the string replacement, then writes it to the destination. Use getFormula() to pull the actual formula text, then setFormula() after you’ve done your substitution. If your naming gets more complex down the road, throw in some REGEX functions in the script for better pattern matching.
totally agree! apps script is a great way to go. you can grab those formulas as strings, replace _v1 with _v2, and set em back as working formulas. doing it with just sheet functions can be a hassle. good luck with your script!