Forum Discussion
Prevent Name Manager From Duplicating LAMBDA Formulas
The story gets more complicated. I created the Lambdas first and then made the sheet copy and added a new blank sheet without problem. Yet when you do the same, the results are different!
I had wondered whether it could because your Lambdas made explicit reference to worksheet ranges rather than as parameters but that doesn't seem to hold water as a theory.
That might be it. If your Lambdas have in-built references to Tables, each time you copy the sheet, you will generate new tables and hence a new copy of any Lambda function that works off the new Tables.
If that is the case then you could avoid the issue by ensuring that all references appear explicitly as arguments of the Lambda function rather than within its formula. A useful technique is to use
= LAMBDA(table, LAMBDA(parameters, Calculation involving table and parameters ) )(Table1)(paramVal)If you named the Lambdas, the Lambda that has the Table as a required argument should not replicate.
PeterBartholomew1 thank you for that. However, with yours that include no table references, the same thing occurs.
This far, I have never used or derived a Sheet-local named Lambda function although I might consider it in the appropriate circumstances. I have on rare occasion prepared a family of workbook Lambdas, each one named to indicate a specific source data structure (a Table or a multi-dimensional array, say). The Currying technique I showed in the previous post both allows the common elements of the family of Lambdas to be defined and allows the relevant arguments to be accepted from Lambda helper function.
If you can simply delete the sheet-local Lambdas and reverting to the global version changes nothing, then one is led to the conclusion that the local Lambda was created in error. In that case, using the frown to send a documented (and sanitised if necessary) version of the workbook to Microsoft would appear to be in order.