Avatar of Anthony Mellor
Anthony Mellor
Flag for United Kingdom of Great Britain and Northern Ireland

asked on 

LAMDA inside Structured Table - syntax to pass variables?

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)

Open in new window


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])

Open in new window

=SUMIF(INDEX([CQNO],1):[@CQNO],[@CQNO],INDEX([Settled],1):[@Settled])

Open in new window

I am using the INDEX solution, or trying to. It works perfectly on its own.

Anthony
Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Anthony Mellor

8/22/2022 - Mon