The Lambda function and its reliance on the Excel Name Manager

[George_Rickerson]

I am using a gaming laptop with Windows 10 and Office365. 

I have spreadsheets with complex formulas. I have been trying to use the Lambda function to replace these formulas, but I am running into issues because of the Lambda function's reliance on the Excel Name Manager.  I have a specific example of the problem:

In my gradebook (I teach an online class), on the sheet where I use the Lambda function, the students' last names are in column 2, beginning in cell C2 and extending down several rows, depending on how many students are in the class. The name of my Lambda function is "Homework".

I have a name, "LastName", defined, and the "Refers To" value is $C2.  The problem is that I cannot depend on this Refers To value, as the Name Manager keeps changing it.  The formula that uses this name is a formula that calculates a grade value for each student's homework for each week of the session.  When I enter "=Homework(6)" in the cell for the homework of the students, with the "6" referring to the week (this value will be anything from 1 to 7 during a session), the function usually returns an incorrect value until I go into the Name Manager and fix the Refers To for "LastName".

The Name Manager usually has changed the "Refers To" to $C$1; sometimes it's $C1048576.  It is NEVER, when I'm first starting out to do grades for a given week, the reference I specified when I created the name ($C2).

As far as I am concerned, this is a fatal flaw for the Lambda function.  If I cannot depend on the name references I specified being stable, how can I depend on the Lambda function to return a correct result? Why must I always check the references to make sure Excel hasn't changed them?

I understand that I could fix this problem by using an explicit cell reference in the Lambda formula instead of an Excel name. The only cell that must be named for a Lambda is the one used to pass a value to the Lambda.  This, in my opinion, misses the point.

What if I want to use Excel names for all of my variables in the formula, and what if one of those names has a Refers To value of $C2, i.e., the column is fixed but the row changes as the formula is calculated? 

I think Lambda's dependence on the Excel Name Manager is a significant weakness that compromises its usefulness. I have seen no disclaimers that warn a user that using Excel names in Lambda formulas may result in the Lambda returning an incorrect result for certain forms of names.

George Rickerson

[#2]

@George_Rickerson 

George, names in Excel are connected, but these are different things. You may use LAMBDA only with local names without global names in name manager manager, if only to name lambda function.

Names with relative references are always works such way as you described, independently on lambdas. If you stay in row #3 and define the name as reference on $C2, in any cell in second row name will show $C1, if you stay in first row it shows $C1048576 since you are out of grid range and reference reverted on the last row of the grid.

Lamdas are oriented to work with arrays. In your case that could be range $C$2:$C$50 or so named as lastName and within lambda you calculate where is data for week 6 depends on your data structure logic, and return desired value.

You don't need relative cell references working with arrays and lambdas.

[#3]

@George_Rickerson I find the lambda function useless in name manager. Even if I try to use it in another different cell in the same worksheet where I defined it, it doesn't work the same way. Using it on a different sheet or different place in the same sheet is impossible. Here is an example I named splitString:

=LAMBDA(string,delimiter,TRIM(MID(SUBSTITUTE(string,delimiter,REPT(" ",LEN(string))),(COLUMNS($B:B)-1)*LEN(string)+1,LEN(string)))

Note the column array $B:B. Reference the string "aa bb cc" and add delimiter " " and copy across 3 columns to populate 3 cells with aa in first cell, bb in second cell, and cc in third cell. If you use column C to start then $B:B becomes say $B:C and it fails to populate properly. The problem is that $B:B changes depending on the column you start the function.

If you use this instead without any cell references in the formula:

=LAMBDA(colArray,string,delimiter,TRIM(MID(SUBSTITUTE(string,delimiter,REPT(" ",LEN(string))),(COLUMNS(colArray)-1)*LEN(string)+1,LEN(string)))

then use it like this 

=splitString($B:B,string,delimiter)

you can use it anywhere as long as you use $B:B or  any adjacent columns such as $F:F etc. COLUMNS(colArray) is used as a counter. This can be used on any spreadsheet in the workbook adjusting the column array and string reference appropriately.