how to convert text into a formula

I've got a huge workbook which I'm trying to make more efficient, by eliminating as many INDIRECT and other volatile formulas as possible.

I've attached a small piece of it, showing my existing INDIRECT formula, and an attempted solution in which I use CONCAT (which doesn't create a working formula but rather shows it as text).

Normally I'd just manually point the cell directly to the worksheet where the desired # is, but I have 95 worksheets (all same layout) and am hoping for a non-volatile formula-based solution.

Thanks!

VBA provides an Evaluate(string) function that does what you want but it is not directly accessible from the worksheet. To make it accessible you can write a VBA eval function like this :

Please Login or Register  to view this content.

You would use it like this:

=eval(H8)

Note, though, that it is volatile, as it wouldn't know when to recalculate when necessary, so it rather defeats what you are trying to do.

Hope this helps.

Pete