Forum Discussion
Passing a Table as a variable (in a LAMBDA() function)
Hi,
Why can't I do this (in Excel 356)? I have a table called Table
I'm trying to build a LAMBDA() function that will contain something like the following (this is the test syntax...):
=LAMBDA(T,T[#Headers])(Table)
But the T[#Headers] statement is obviously just flat out illegal, because the editor won't even accept it! ("There is a problem with this formula..."). Why is that? How can I construct a reference to a Table that will allow me to pass the Table as a variable and then grab a range from it?
Thanks.
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.
ColinJHarrison In that formula the first T is equivalent to the table array called T. Enter =Table somewhere and the entire table excluding the headers will be spilled.
The second T combined with [#Headers] merely refers to the table name. So, the LAMBDA needs to concatenate the table name with [#Headers] and use INDIRECT to reference it.
This is demonstrated in the picture below.
A1 contains a formula referencing the top left cell of the table.
A2 extracts the table name (I named the cell "TableName").
A4:B7 contains the table.
A9 shows what T would do.
A13 is the LAMBDA based on the table name.
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.
