Does anyone know the syntax for calling a Lambda via INDIRECT? I can write =Calipso!audit(1000) but I cannot write =INDIRECT("Calipso!audit")(1000) nor =INDIRECT("Calipso!audit(1000)") Any pointers? Thanks!

ecovonrein Afraid that doesn't work, as with any other named functions. As variant you may create for the set of you functions something like one = LAMBDA(x, x+1); two = LAMBDA(x, x+2); callFunction=LAMBDA(str, SWITCH( str, "one", one, "two", two) ); and call it as

ecovonrein Since I am not sure I fully understand the use case for INDIRECT, here are some techniques that might be of relevance. I started with a validation list of function names as text. That allows me to use validation to select the desired function names. I then built an array of functions in memoryFunctionList = VSTACK(SINλ, COSλ, TANλ) where SINλ = LAMBDA(ϑ, SIN(RADIANS(ϑ))); COSλ = LAMBDA(ϑ, COS(RADIANS(ϑ))); TANλ = LAMBDA(ϑ, TAN(RADIANS(ϑ)));The worksheet formula could then be= LET( Fnλ, XLOOKUP(function, validationList, FunctionList), MAP(Fnλ, ϑ, Applyλ) ) where Applyλ = LAMBDA(Fnλ₁, ϑ₁, Fnλ₁(ϑ₁))If you find having formulas on the worksheet distracting or ugly, you could wrap the whole shooting-match within a further Lambda to give= Indirectλ(function, ϑ)

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.

Thanks. Not great. I was surprised that I can defineaudit=Calipso!auditand call=audit(1000)Extrapolating from there, I expected=INDIRECT("Calipdo!audit")(1000)to work.Your SWITCH does not work for me because the list of possible targets is non-exhaustive, as is the places from where the call might come.Well, there's hoping that MS will get around to fixing it at some point. (It isn't core functionality for me at this mo.)

ecovonrein 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.

Syntax for calling Lambda via INDIRECT | Microsoft Community Hub

SergeiBaklan
MVP
Apr 29, 2023
ecovonrein

Afraid that doesn't work, as with any other named functions. As variant you may create for the set of you functions something like

one = LAMBDA(x, x+1); two = LAMBDA(x, x+2); callFunction=LAMBDA(str, SWITCH( str, "one", one, "two", two) );

and call it as
- ecovonrein
  Iron Contributor
  Apr 29, 2023
  Thanks. Not great. I was surprised that I can define
  
  audit=Calipso!audit
  
  and call
  
  =audit(1000)
  
  Extrapolating from there, I expected
  
  =INDIRECT("Calipdo!audit")(1000)
  
  to work.
  
  Your SWITCH does not work for me because the list of possible targets is non-exhaustive, as is the places from where the call might come.
  
  Well, there's hoping that MS will get around to fixing it at some point. (It isn't core functionality for me at this mo.)
  - JosWoolley
    Iron Contributor
    Apr 30, 2023
    ecovonrein
    
    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.
PeterBartholomew1
Silver Contributor
May 01, 2023
ecovonrein
Since I am not sure I fully understand the use case for INDIRECT, here are some techniques that might be of relevance. I started with a validation list of function names as text. That allows me to use validation to select the desired function names. I then built an array of functions in memory
FunctionList = VSTACK(SINλ, COSλ, TANλ) where SINλ = LAMBDA(ϑ, SIN(RADIANS(ϑ))); COSλ = LAMBDA(ϑ, COS(RADIANS(ϑ))); TANλ = LAMBDA(ϑ, TAN(RADIANS(ϑ)));
The worksheet formula could then be
= LET( Fnλ, XLOOKUP(function, validationList, FunctionList), MAP(Fnλ, ϑ, Applyλ) ) where Applyλ = LAMBDA(Fnλ₁, ϑ₁, Fnλ₁(ϑ₁))
If you find having formulas on the worksheet distracting or ugly, you could wrap the whole shooting-match within a further Lambda to give
= Indirectλ(function, ϑ)
SelectingFunctionsUsingXLookup.xlsx17 KB
- ecovonrein
  Iron Contributor
  May 02, 2023
  This is essentially the same approach that Sergei takes with SWITCH. It does not work when the target Lambdas are in a foreign Workbook.
  
  I have wondered whether the challenge of poking numbers out of a time grid might be a suitable matter for Power Query. (I don't know this thing, really.) The grid is highly readable to a human so I would certainly expect the Copilot to be able to do it. Not that I would know where to start 🙂🙂...
  - PeterBartholomew1
    Silver Contributor
    May 02, 2023
    ecovonrein
    I am more used to using a scratch worksheet in the same book for testing/purposes so I have little experience of the scenario you outline. At least I now have some idea as to how the sheet local names arise for your Lambda functions. I tend to use workbook scoped Lambdas and keep the sheet localisation to the parameter names rather than the function, but I appreciate that may not be possible.
PeterBartholomew1
Silver Contributor
May 01, 2023
ecovonrein
I think I am beginning to lose the thread here. What is the intended purpose of the indirect?
If the top level formula is a Lambda function, one can pass any other function as a Lambda variable and apply it to such parameter string as one chooses within the inner formulae. That would be cleaner conceptually than identifying the Lambda as a text string and using INDIRECT.

Forum Discussion

Syntax for calling Lambda via INDIRECT

Share

Resources