Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

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 OfficeMicrosoft Excel
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
Avatar of zorvek (Kevin Jones)
Commented:
This problem has been solved!
Unlock 1 Answer and 36 Comments.
See Answers