Forum Discussion
Passing a Table as a variable (in a LAMBDA() function)
ColinJHarrison I think the point you are missing is that Table1 may refer to the table itself as a dynamic array and to the name of the table which is no more than a text. The LAMBDA needs to refer to the table name.
Remove the quotes in A2 and change the formula to this:
=LAMBDA(Tn,INDIRECT(Tn&"[#Headers]"))(A2)
Similar for the named LAMBDA function. You want to feed it the text that is in A2, so
=TPX(A2) will work.
See attached.
Hi Riny (are you the only person on this forum? Haha)
I had tried INDIRECT(), but it doesn't give me the expected result.
If I have a table called Table1, and I call =Table1[#Headers], (as I have in cell D10 in the attached image/sheet) then I get the header row values spilling horizontally from D10, as I would expect.
I would have thought that calling =LAMBDA(Tn,INDIRECT(Tn&"[#Headers]"))(Table1) should do exactly the same thing, but it doesn't. What I get is a spill range the same dimensions as the whole Table, filled with #REF! errors, which are "Invalid Cell Reference" errors. See the screenshot below, in which D4 contains the LAMBDA() call shown in red in D2.
Just in case it was an issue with the LAMBDA() test syntax, I assigned the function to a name (TPX()) in the Name Manager and called it directly from cell G4 (=TPX(Table1)), which as you can see, has exactly the same result.
I am perplexed. I have attached the sheet below.
Cheers,
Colin
ColinJHarrison I think the point you are missing is that Table1 may refer to the table itself as a dynamic array and to the name of the table which is no more than a text. The LAMBDA needs to refer to the table name.
Remove the quotes in A2 and change the formula to this:
=LAMBDA(Tn,INDIRECT(Tn&"[#Headers]"))(A2)
Similar for the named LAMBDA function. You want to feed it the text that is in A2, so
=TPX(A2) will work.
See attached.
- OK. That makes sense. I guess I was thinking I needed to pass a ref to the actual dynamic range rather than just to its name. But of course that wouldn't make sense for an INDIRECT() call, eh... Hm. In any case, I can see how to make what I'[m trying to do work now, so, once again, thank you for your assistance!
I'm trying to do the same thing, pass a table to a lambda function. But I would greatly prefer a solution without the use of INDIRECT as it slows calculations way down as it is volatile. (Calculates every time anything changes.)
Anyone have a solution for passing a table to a lambda function that does not use INDIRECT?
- Ok, figured it out, use INDEX instead of INDIRECT. Here is an example that returns the data from a column in a table:
=LAMBDA(TableAll,ColumnTitle,DROP(INDEX(TableAll,0,MATCH(ColumnTitle,INDEX(TableAll,1,0),0)),1,))(Table1[#All],"Cost")
Found a useful discussion:
https://superuser.com/questions/1713772/dynamic-table-name-in-excel-lambda-function-with-structured-references-without-i
