Apply a string value to the active cell as a real formula with cell reference

[grexcelman]

I'm halfway to solving this dilemma of mine. I have a table listing dozens of formulas some of which are custom built through a 3rd party API like Bloomberg financial(call that "FormulaTable") while others are more custom designed. A formula can be selected based on two criteria from a data table, "ApplicationTable" where I hope to apply the selected formula given a relative cell value in that table.

Because there's about a dozen different categories in the ApplicationTable, and a given formula choice is dependent on two different field options, an IF statement was not option, nor was a Vlookup. I've solved this part of the problem with an function that uses index match code in vba and named that string variable, "GetMetric" -

Specifically, what I'd like to do is have that string value(the formula chosen from the FormulaTable), get applied to my ApplicationTable as a working formula in column F and apply it with a relative cell reference to column B.

Any ideas how to express a string value as an active formula? I've seen some ideas with evaluate function but couldn't seem to get the formula to work, instead it just printed the string value in the active cell.

 

[davesweep]

Hi,

I think you'll need to do this in VBA

this code

range("b1").formula = range("A1").Value

Would take the value in cell A1 and apply it as a formula in B1. Does this get you started?

 

[grexcelman]

Hi,

I think you'll need to do this in VBA

this code

range("b1").formula = range("A1").Value

Would take the value in cell A1 and apply it as a formula in B1. Does this get you started?

Hi Dave, thanks for your reply. I suppose after I lookup my text I could print the string in an adjacent cell. then have code or a cell formula that interprets it such as the formula you wrote. How would this ultimately become an active formula where I could apply it to referenced cells? For example, if the string returned is 'Average', I'd like for the Average function to average cells 'C:F' -- if the string returned is 'STDEV', I'd like that applied on C:F instead. is this doable?

 

[davesweep]

It's achievable for sure. Can you give some examples that show

a) the text that you want to turn into a formula
b) the formula that you're expecting

 

[grexcelman]

It's achievable for sure. Can you give some examples that show

a) the text that you want to turn into a formula
b) the formula that you're expecting

I'll try to send again shortly. I was timed out after I finished typing up a reply and it deleted everything including the tables I built to display as examples.

 

[grexcelman]

It's achievable for sure. Can you give some examples that show

a) the text that you want to turn into a formula
b) the formula that you're expecting

I have two tables, one will house the formulas chosen by a lookup, and the other consolidates the data depending on what's in columns A and B respectively. For simplicity sake I only provided a couple of rows and just information pertaining to the first metric. I anticipate having several hundred rows of data with roughly a dozen varying metrics to be applied from the FormulaTable.

[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD]Application Table
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ticker
[/TD]
[TD]Sector
[/TD]
[TD]Metric1
[/TD]
[TD]Metric2
[/TD]
[TD]Metric3
[/TD]
[TD]Metric4
[/TD]
[/TR]
[TR]
[TD]AAPL
[/TD]
[TD]TTM
[/TD]
[TD]=BFP("A2","Price_to_Sales",FY)
[/TD]
[TD]GM
[/TD]
[TD]PEG
[/TD]
[TD]P/RAW
[/TD]
[/TR]
[TR]
[TD]ABBV
[/TD]
[TD]Biotech
[/TD]
[TD]=BFP("A3","Price_to_Sales",FY)
[/TD]
[TD]PEG
[/TD]
[TD]P/PIPE
[/TD]
[TD]EV/EBITDA
[/TD]
[/TR]
</TBODY>[/TABLE]


[TABLE="class: grid, width: 400"]
<TBODY>[TR]
[TD]Formula Table
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sector
[/TD]
[TD]Ticker
[/TD]
[TD]Metric1
[/TD]
[TD]Metric2
[/TD]
[TD]Metric3
[/TD]
[TD]Metric4
[/TD]
[/TR]
[TR]
[TD]Biotech
[/TD]
[TD]AMGN
[/TD]
[TD]'=BFP("A2","Price_to_Earnings",FY)
[/TD]
[TD]PEG
[/TD]
[TD]Profit Mgn
[/TD]
[TD]Gross Mgn
[/TD]
[/TR]
[TR]
[TD]Biotech
[/TD]
[TD]ABBV
[/TD]
[TD]'=BFP("A2","Price_to_Sales",FY)
[/TD]
[TD]PEG
[/TD]
[TD]P/PIPE
[/TD]
[TD]EV/EBITDA
[/TD]
[/TR]
[TR]
[TD]Finance
[/TD]
[TD]JPM
[/TD]
[TD]'=BFP("A2","Price_to_Book",FY)
[/TD]
[TD]P/E
[/TD]
[TD]EV/EBITDA
[/TD]
[TD]D/E
[/TD]
[/TR]
[TR]
[TD]TTM
[/TD]
[TD]AAPL
[/TD]
[TD]'=BFP("A2","Price_to_Sales",FY)
[/TD]
[TD]GM
[/TD]
[TD]PEG
[/TD]
[TD]P/RAW
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[davesweep]

Sorry for the delay in replying.

So you have a formula in each table (Metric1) that's present as text(?) that you need to be converted to an active formula?

 

[grexcelman]

No worries. That's correct. My code will pick through a table of formulas listed as text and after retrieved based on a vlookup/index-match I want my code to insert the text as a formula that can reference a cell in the retrieval table(ApplicationTable).

 

[davesweep]

So you code either use the code above, or create a UDF utilising the evaluate function..

Function ConvertTextToFormula(rInput As Range) As Variant
fOutput = Evaluate(rInput.Value)
ConvertTextToFormula = fOutput
End Function

Is that any use at all or am I missing the point?

 

[mikerickson]

You might consider using R1C1 notation in your Metric1 column, so you don't have to adjust for formulae being put into different cells.