Forum Discussion
Syntax for calling Lambda via INDIRECT
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
- 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.)I don't think that will be fixed. No difference with named regular formulae. E.g., if you define mySum as =SUM(range), when =mySum works but =INDIRECT("mySum") doesn't work. INDIRECT() requires references.
I'm not sure why you can't modify SWITCH every time you add new function, but that's your choice.
When, if you don't use Excel on web and macro enabled workbooks are allowed - EVALUATE() as suggested above.
Uff. I have multiple very heavy production models. None of those use INDIRECT (or any other ghastly volatile functions). However, each year end during the audit process, we like to poke numbers out of these models, from ancillary auditing spreadsheet. These spreadsheets can use INDIRECT etc since performance is not a concern. I like to construct these little helper sheets with INDIRECT because then I can simply input the current name of the financial model and the audit spreadsheet pokes the right file. (It also means that Excel does not create any hard links that import x MB of data.) Now, I have just spent a few months reprogramming one of these monsters to take full advantage of Lambdas and Spills. Each monster contains multiple company valuations, each in a separate Worksheet. Each Worksheet is a copy of a clean master and I taught the master a little Lambda called "audit" which allows an outsider to ask the Worksheet to return practically any number from its grid by symbol (account number, effectively). Now, it would have been nice to be able to invoke this audit function INDIRECTly from the auditing spreadsheet...
The way I do the same stuff in the previous generation of models is by means of a symbol search (to obtains an A1 reference in the foreign sheet) and a conventional INDIRECT call (with that reference). I was hoping I could do better. But it seems I will have to stick with the old technique. Too bad.
PS: I might try EVALUATE though I am unsure what I might be evaluating.PPS: You might now understand that in this world, I do not know the names of the branches for your SWITCH - they are the Worksheets of the Workbook. I would need a VBA macro to auto-generate an appropriate SWITCH per Workbook. Not that it would help, since we would need your SWITCH in the foreign (auditing) Workbook. Effectively, that Workbook would need to run the VBA macro to generate an appropriate SWITCH each time the name of the foreign Workbook is updated. PITA...
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 Functionthen 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.
