Copper Contributor

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? 

3 Replies


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.

thank you. got it!

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