Forum Discussion

sckats's avatar
sckats
Copper Contributor
May 19, 2024

Lambda

I used this Lambda function:

"=LAMBDA(range,IFERROR(TOCOL(FILTER('3 times'!K3:CL3,(range=3))),""))(K4:CL4)" and it worked perfectly.

I then inserted it into name manger, created a name and it fails to work. It seems to input the wrong cell references? 

  • sckats 

    You use relative reference within the formula. Let say you stay in A1 and add above formula in Name Manager. Within formula it'll be '3 times'!K3:CL3

    Now stay on any other cell, let say A3 and check the formula. Now you see within it '3 times'!K5:CL5

    Not sure what exactly you'd like to do, if '3 times'!K3:CL3 shall not be changed perhaps use the name for this range.

  • Balint79's avatar
    Balint79
    Brass Contributor
    sckats

    Sergei already properly pointed out, that ranges need a lock, so would have just some "context concerns":
    --if the LAMBDA() was used several times before without Name manager, then it was not needed, since contentwise this formula does the same "TRANSPOSE(FILTER(K3:CL3;3 times!K4:CL4=3))" however understand that you need the two FILTER arguments from different tabs
    --the IFERROR() is not needed, since the FILTER() has its own last argument for CALC error (ie empty filtering)
    --instead of TOCOL() would use only just a TRANSPOSE()

Resources