troubleshooting Question

LAMDA inside Structured Table - syntax to pass variables?

Avatar of Anthony Mellor
Anthony MellorFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft ExcelMicrosoft Office
36 Comments1 Solution18 ViewsLast Modified:
So this works, but it's parameters are not set. For example CREFS should be replaced with INTABLE. When I do that it fails.So this works, but it's parameters are not set. For example CREFS should be replaced with INTABLE. When I do that it fails.

Preceding question https://www.experts-exchange.com/questions/29214447/How-to-reference-a-header-in-a-structured-table-without-an-error.html#a43280612


So this works, but it's parameters are not set. For example CREFS should be replaced with INTABLE. When I do that it fails.

=LAMBDA(INTABLE,ifthis,thenthat,SUMIF(INDEX(CRefs[[#All],[CQNO]],1,0):[@CQNO],[@CQNO],INDEX(CRefs[[#All],[Settled]],1):[@Settled]))(CRefs,CQNO,Settled)

LAMDA can be made to work inside the structured table in its draft form, meaning not yet created as a name. When I change the first CREFS mentioned above to INTABLE,so as to give effect to the first parameter we get this:



This feels very much like a syntax problem. For example when attempting to enter it in to the name space, it is outside the table which is why I have the full name typed in to it. I have noticed that if preparing the LAMBDA outside the table with full field names, then copying it in to the table, Excel removes the table name from the field names.

and this time I have tried both keyboard and mouse creation methods.

So, image is, with file after:

EE-LAMBDA-IN-TABLE.xlsx

Incidentally, my plan is not to make this whole LAMDA as a LAMDA, but to split it in to a sort of set of tools I can use, probably in tandem with LET, but first it needs to work at all.

Here is my underlying formula question link:

=SUMIF(CRefs[[#Headers],[CQNO]]:[@CQNO],[@CQNO],CRefs[[#Headers],[Settled]]:[@Settled])
=SUMIF(INDEX([CQNO],1):[@CQNO],[@CQNO],INDEX([Settled],1):[@Settled])
I am using the INDEX solution, or trying to. It works perfectly on its own.

Anthony
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 36 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 36 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros