Forum Discussion
Syntax for calling Lambda via INDIRECT
You won't be able to achieve what you want using INDIRECT, though if a macro-enabled workbook is an option then perhaps you can find a workaround using EVALUATE.
For example, with cell A1 containing the defined name pertaining to the LAMBDA, a further definition within Named Manager, Eval, say, as:
=LAMBDA(ζ,EVALUATE($A$1&"("&ζ&")"))
after which you can call, for example:
=Eval(1000)
within the worksheet.
JosWoolley PeterBartholomew1 SergeiBaklan
Quick update as I am in the middle of our dreaded audit. There is a way to skin this cat that is not obvious at all. It all centers on EVALUATE, which (if one thinks about it) isn't greatly different to INDIRECT anyway.
But we must not, as was suggested, call EVALUATE from
EVAL=LAMBDA(expr,EVALUATE(expr))
This does not work because while
=EVAL("Calipso!audit(a,b)")
will gladly run audit(a,b) in the Worksheet "Calipso" within the current Workbook, it is NOT POSSIBLE to invoke EVAL from a foreign Workbook. The formula
='myWorkbook.xlsm'!EVAL("Calipso!audit(a,b)")
will fail - even though the formula
='myWorkbook.xlsm'!EVAL("Calipso!A1")
works perfectly well.
However, if we create EVAL as a tiny UDF in VBA inside myWorkbook.xlsm like so
Public Function EVAL(expr As String) As Variant
EVAL = thisWorkbook.Worksheets(1).Evaluate(expr)
End Function
then the cross-Workbook call
='myWorkbook.xlsm'!EVAL("Calipso!audit(a,b)")
suddenly works. The important trick inside this code is the provision of "thisWorkbook.Worksheets(1)" as the explicit context. The simple code
EVAL = Evaluate(expr)
otherwise executes in the (wrong) context of the invoking Workbook (and probably returns #REF!).
NB:
My Excel is tuned to the European convention and I hence normally separate function arguments with ";". However, VBA does not alter its syntax depending on locale. Since EVAL is executed thru VBA, counter-intuitively for a European user the expr must use "," to separate the arguments to the Lambda.
PS:
I am a bit flummoxed because I could have sworn that in a past life I needed to enable Excel 4.0 macros in Trust Center before I could use EVALUATE. No such problems today. Odd.