Let and Lambda

[Bob Phillips]

I have to admit that, whilst I am not convinced that these are the way to go for Excel (most Excel users don't want to 'program' Excel, and most 'useful' Lambda functions that I see are pretty complex, and I find that I struggle to understand Lambda functions that I wrote when I revisit them), I am using them an awful amount in my own workbooks.

One thing I especially like to do with Let formulae is to declare my variables up top, rather than in the body of the function. In a very simple example, rather than

=LET(res, FILTER($A$1:$A$500, $B$1:$B$500="Transfer"),
         res)

I would write

=LET(method, "Transfer",
         res, FILTER($A$1:$A$500, $B$1:$B$500=method),
         res)

One issue I have come up against though is that I don't see to be able to setup a table variable in this way. Take this example formula

=LET(tx, tblTransactions,
          method, "Transfer",
          tx_date, TODAY(),
          res, FILTER(tx[Date], (tx[Method]=method)*(tx[Date]>=tx_date)),
          res)

I could use INDIRECT for the table, but I don't want to do that.

I could also define each of the table columns separately and reference those, but I was wanting to have a single point defining the table.
Does anyone else have this issue? Has anyone cracked it.

[georgiboy]

Not sure you can refer to a column in a table you have defined like 'tx[Date]'

If you define the table, in my mind it becomes more of a range to the LET formula, as such you can refer to the columns with Index:

=LET(tx, tblTransactions,
          method, "Transfer",
          tx_date, TODAY(),
          res, FILTER(INDEX(tx,,1), (INDEX(tx,,2)=method)*(INDEX(tx,,1)>=tx_date)),
          res)

Other than that you would use the table name itself:

=LET(method, "Transfer",
          tx_date, TODAY(),
          res, FILTER(tblTransactions[Date], (tblTransactions[Method]=method)*(tblTransactions[Date]>=tx_date)),
          res)

If you renamed the table 'tx' then 'tx[Date]' would be fine.

[Bob Phillips]

If you define the table, in my mind it becomes more of a range to the LET formula

You may be right there, but if so that is poor implementation of LET in my view. As with other instances, CF, row grouping, for example, the Excel guys have added functionality without consideringfor tables, even though they are pushing tables as the way to go.

, as such you can refer to the columns with Index:

=LET(tx, tblTransactions,
          method, "Transfer",
          tx_date, TODAY(),
          res, FILTER(INDEX(tx,,1), (INDEX(tx,,2)=method)*(INDEX(tx,,1)>=tx_date)),
          res)

No, that is horrible. It is referring to columns in the table by position not by name, which is wholly at odds with the rationale of structured tables.

Other than that you would use the table name itself:

=LET(method, "Transfer",
          tx_date, TODAY(),
          res, FILTER(tblTransactions[Date], (tblTransactions[Method]=method)*(tblTransactions[Date]>=tx_date)),
          res)

That is exactly what I am doing now, but that means that I reference the table name 3 times rather than just once, not good coding practice.

If you renamed the table 'tx' then 'tx[Date]' would be fine.

No way! I have very clearly defined coding standards

[Aussiebear]

hehehe.... ze English are firing upon themselves. I shall make my escape into the darkness.

[Paul_Hossler]

hehehe.... ze English are firing upon themselves. I shall make my escape into the darkness.

Us colonials will also make our escape to the familar land of VBA and UDFs

[Bob Phillips]

It's all friendly guys, or to use a horrible phrase, just banter. Georgiboy basically confirmed what I thought, I can't have my cake and eat it .

[Aussiebear]

So was ours Bob. Must admit it is good to see you here.

[georgiboy]

It is indeed good to see you back Bob, I did learn a lot from your posts years ago...

It's all friendly guys

It was taken as such, you are as frustrated as me at the way Excel has progressed.

You may be right there, but if so that is poor implementation of LET in my view. As with other instances, CF, row grouping, for example, the Excel guys have added functionality without considering for tables, even though they are pushing tables as the way to go.

I can only agree, another annoying issue is that you can't use array formulae in a table, the formulae are unable to expand the table, this is but another missed opportunity.

No, that is horrible. It is referring to columns in the table by position not by name, which is wholly at odds with the rationale of structured tables.

Again I agree, however, this is the method I use quite a bit, only on tables where I know the structure of the table will not change.

That is exactly what I am doing now, but that means that I reference the table name 3 times rather than just once, not good coding practice.

No way! I have very clearly defined coding standards

Again I have to agree, however, this is not technically "Coding" by my own definition

[Aflatoon]

You may be right there, but if so that is poor implementation of LET in my view.

I don't see that as anything to do with LET though. LET is basically just a formula version of Dim (or indeed Let in M code). The limitation is just a(nother) limitation of tables, as you say. To refer to columns by name, either in LET or any other formula, you need to use INDIRECT or match the name in the headers to get a column number to use in INDEX.

[Bob Phillips]

Even Rory is still here, it is almost like the old days, but with more Excel gizmos!

I don't see that as anything to do with LET though. LET is basically just a formula version of Dim (or indeed Let in M code). The limitation is just a(nother) limitation of tables, as you say. To refer to columns by name, either in LET or any other formula, you need to use INDIRECT or match the name in the headers to get a column number to use in INDEX.

I agree that it is, but LET in Excel had to be built (coded), and if they had thought about it I am sure those guys are plenty smart enough to have catered for a full table reference. You can reference a table in VBA, you can reference a table in M, but not it seems in Excel.

[Aflatoon]

You can get a table reference, you just can't refer to columns by name using variables, without the use of INDIRECT or INDEX/MATCH or similar. LET doesn't change the way range referencing or other functions work. Or did I miss your point? Your original formula was basically the equivalent of putting a column name in a cell then trying to use that to refer to a table column - e.g. tblTransactions[A2] - and that won't work with or without LET. If they were going to make LET change the way functions operated, I'd much rather they'd made all the xxxIF(S) functions work with the intermediate arrays you always seem to end up with when using LET, LAMBDA et al!