Build formula from string with reference to other cell/s with operator
I would like to be able to have a formula where in a formula can be build with operators in another cell. As an example where =formula(a1&b1&c1) where the cells would have 1 + 1. in other words b1 would have a + in it. This will make it possible to change operator in a large amount of formulas with only changing input in one cell.
Thomas K
shared this idea
1 comment
-
Roy
commented
MS has avoided this for decades, with nods to the Excel 4 Macro system's EVALUATE() function. But we do need a way to build formulas dynamically without leaving the sheet (EVALUATE() only works inside the Named Range functionality unless you have enabled Excel 4 macros)... in which case you need to write an Excel 4 macro to use it so still not a natural sheetside function!)... some function to wrap the built string and make it become a formula, analogous to what INDIRECT() does for turning strings into cell references.
Need one to turn a string into an actual, working array as well. That seems like another idea altogether at first glance, but really, what we need are ways to turn strings into several different Excel calculational items. Especially now that we can extract formula parts FROM formulas, not just put them in lookup tables (my dear wish in life to break the nested IF() situation) and user choice cells (as mentioned in your Suggestion).
For decades, they didn't provide a direct, sheetside way to get a formula's text since the Excel 4 Macro language offered it. Maybe might not want to correct the deficiencies involved with EVALUATE() for another 35 years...
But they DO seem to want to make Excel more like a programming language (notice SWITCH() a while back and how LET() is supposed to come, someday?). Being able to construct a string and then have it actionable in various ways, including this one, seems a natural for that seeming desire on their part. So maybe there's hope for it in what lifetime I have left.