Dynamic Formula

[Mark McInerney]

Hi All,

I want to dynamically link the entry in a cell to another formula - I tried using the indirect function, but cannot seem to get it to work.

Is this possible - what I am trying to get in Cell B4 is the number 5, and not "2+3". When working correctly I should be able to change Cell C2 to "-" or ">=" or "<=" or ">" and so on.

Thanks - Mark.

 

[lrobbo314]

Using a VBA custom function.

Book1 (version 1).xlsb
	B	C	D
8	2	+	3
9	5
Sheet8

Cell Formulas
Range	Formula
B9	B9	=eval(B8&C8&D8)

Function eval(s As String)
eval = Evaluate(s)
End Function

 

[Mark McInerney]

HI,

Thanks for taking the time to review - when I put in the formula an error message comes back - I assume it is to do with the VBA code? Is there a way to bypass the VBA code required?

Thanks - Mark.

 

[lrobbo314]

Could use Power Query instead. Not sure if there is a way to do it using built-in formulas.

Book1 (version 1).xlsb
	I	J	K	L
8	N1	Operator	N2	Table5
9	2	+	5	7
Sheet8

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Eval = Table.TransformRows(Source, each Expression.Evaluate(Text.Combine(List.Transform(Record.ToList(_),Text.From))))
in
    Eval

 

[Joe4]

HI,

Thanks for taking the time to review - when I put in the formula an error message comes back - I assume it is to do with the VBA code? Is there a way to bypass the VBA code required?

Thanks - Mark.

You just need to drop that VBA code he posted in a blank General VBA module in that workbook, so that the formula recognizes it.

I personally do not know of any way to do it just with Excel formulas, and not involving something like VBA or Power Query.

 

[lrobbo314]

Did find another way. If you make a named range using the evaluate function you can do it without VBA.

Named Range Definition: =EVALUATE(Sheet8!B9)

I also named the range: xVAL

Book1 (version 1).xlsb
	B	C	D
8	4	+	3
9	4+3
10	7
Sheet8

Cell Formulas
Range	Formula
B9	B9	=B8&C8&D8
B10	B10	=xVAL