If you are willing to believe me that a thing exists long enough to actually try it, there is at least one way.
Before VBA, Excel had a macro language. When they added VBA to the program, they kept the old macro language. People supposed it was for backward compatibility and that it would, as hinted, disappear in some future version. Well, it never disappeared. It's actually fully functional and the who shebang is there if one wants to activate it.
You don't need "the whole shebang" though and very happily, those functions it had that have meaning, so to speak, without using the full package, work even today. In fact, there's some evidence in MS's online aids that it can never go away, so it might exist forever and everything work like it does 25 years after they began saying it might go away.
The thing is though, they do not work outside either (your choice) actual Excel 4 macros (the whole shebang installed, and building macro pages with it) OR inside Named Ranges for the functions that Excel can make sense of outside their macro purposes.
EVALUATE() is the function of interest here. It can do to a formulaic string (any string that would make sense if it were entered after an = what INDIRECT() does to any string that forms a proper address.
And that's exactly what you are asking to have. Outside of VBA, an actual "performing kind" of macro or a UDF you write, it is the only way available in Excel. So if you don't know, or don't want to use, a VBA solution but rather need something that works in Formula World, then this is the only thing. Almost, to be honest, but certainly the easiest and most easily understood and built.
So, first you need your set-up. You have that, a place you want the output of it all, and a place where you have your formulas. Substiutute into the following as needed. What I do below is for data in cells A1:A5, three formulas I might like to use in B1:B3, a lookup value in C1, a cell reference for the formula I desire in C2, and a Named Range whose content I shall show now:
=EVALUATE(FORMULATEXT(INDIRECT(Sheet1!$C$2)))
Again, this will not work cell-side. It only works in the Named Range, nowhere else.
INDIRECT() takes the cell reference in C2 and passes it to FORMULATEXT() which procurs the formula in the cell specified in C2. That text string is passed through to the EVALUATE() function which treats it "like a real boy." Entering the formula:
=horse
(horse being the name I used for the Named Range)
in the cell you want the output gives you what you want.
An alternative is to build your formulas to be VERY relative. Probably have to build a set made relative for each cell in your work where you want their potential output. And extra care would have to be taken if they are to be "copied down" a column. So even though such a thing could conceivably be done, it would be a monstrous nightmare to set up, another to expand the usage of, and a further one to use down a column.
EVALUATE(), on the other hand, is simple and straightfoward. It fits precisely what you have already done. It is the last, simple and easy to use, last step you need in what you've done already.
But try EVALUATE() outside the Named Range functionality, try to use it inside a cell, and it will tell you there's no such thing. Stick with that and you'll not have success but rather will join the list of folks who get it wrong, then insist it just doesn't exist rather than figure out what went wrong. I feel you want success, so remember: use EVALUATE() in a Named Range, or not at all.
