Convert function string into formula

[golden-berg]

After multiple fruitless searches I'm turning to the experts.
I'm using Excel 2016 32-bit stand-alone (not 365)
I have a table with snippets of formulas, which I'd like to access through a lookup formula in order to construct larger formulas.
In the spreadsheet attached, tab "Table" has the formula snippets while tab "usage" has the XLOOKUP areas and data. Using the VBA

>>> use code - tags <<<

Function Eval(strTextString As String)
     Application.Volatile
     Eval = Evaluate(strTextString)
End Function

I'm showing that I have a solution for the simplest of snippets, but how do I create and retrieve snippets such as those shown in column "What I need to see" - note that, for instance, for (g4-j4)-h4 the number corresponds to the row that it's on, etc.
Thanks very much for any guidance.

 

[Peter Bartholomew]

I do not think I understand the purpose of this construct. There are better ways of referencing a cell on the same row as the formula than constructing an address as text and using VBA. The formula snippets could also be inserted into a CHOOSE or SWITCH function if you need to switch formula by entering data.

Why does XLOOKUP appear within an Office 2016 file? LOOKUP would seem to do what you require.

 

[golden-berg]

First of all, thank you very much for your response.
My example was a simplification of my problem, which was perhaps not explained sufficiently. To be more complete, in a table, the value in a certain cell depends on formulas which are themselves selected as a function of values in different cells on the same row. Here's the inelegant way I have addressed one such calculation so far:

=IF(B3="","",IF(S3="CoverdCall",0,IF(Z3="",IF(M3="C-S",(-(K3*($H$36-1))+G3/F3)*F3,((K3-VLOOKUP(D3,AQ:AR,2,0))*-F3-G3)),IF(E3=T4,AC4,(IF(Z3="Bear",IF(Y3="Call",X3-W3+AA3,AA3),IF(Y3="Call",AA3,W3-X3+AA3)))*ABS(F3)))))

What I was trying to do is to go find a snippet of formula from a list driven by value of a cell.
Also I want the snippets to be dynamic, in the sense that the "3" in the example be the number of the row. I don't see how CHOOSE or SWITCH can be used for that .

As for XLOOKUP, I just discovered this function yesterday and want to start using it as it seems very rich; I downloaded ExcelDna.XFunctions.xll from github site and inserted it into my Excel version.

 

[Peter Bartholomew]

I tend to add meaning to any workbook by using defined names (I haven't used a direct cell reference since somewhere about 2014) so my methods are probably not for you. I prefer column names and dynamic array formulas but also use formulas that apply to a list within a Table. In the latter case the structured reference to a cell on the same row is of the form =Table1[@ColumnName]. One can also use intersection, explicit
= thisRow ColumnName
or implicit
= ColumnName [traditional method]
=@ColumnName [dynamic array]
A name can also refer to a relative reference to a cell on the same row (or next row)
CallStatus: =$S3
so that the condition might read
= (CallStatus="CoveredCall")

What these reference methods provide is a way of referencing data without using the construct "S"&ROW().

All this has most likely been of no use to you. My advice would be do not follow the route you have outlined but others may disagree,

 

[golden-berg]

Peter, thank you very much, this suggestion opens an avenue, which I will pursue.
At least intellectually, I'm curious if what I was trying to do ("dynamic snippets" ) is possible in Excel.

 

[Peter Bartholomew]

If you give Evaluate a valid formula string then your idea should work. I have used it to break an array into a sequence of calculations based upon the individual elements of the array but I would only use Evaluate as a last resort for a calculations that cannot be performed using standard worksheet formula.