I have a workbook with a worksheet containing my formulas. in that worksheet I have a named range in a cell called "FORMULA_VERMELL" containing the text "="TMP!A1"" (I plan to contain a more complicated formula in the future, of course)

In another worksheet called TMP, I have a value '123' in the cell A1

in another cell from TMP I am calling my custom vb function TextToFormula:

Public Function TextToFormula(cellRef As String)
    TextToFormula = Evaluate(cellRef)
End Function

... like this: =TextToFormula(FORMULA_VERMELL)

This converts a text containing a formula, into a real formula and evaluates it.

The problem is that if I change the value in A1 from '123' to another value, the cell containing the formula
=TextToFormula(FORMULA_VERMELL)
does not automatically change, as it is not directly referencing the A1 formula, but using the named range. So I have to edit the cell with F2+ENTER for it to recalculate.

Thanks in advance !