Prevent Name Manager From Duplicating LAMBDA Formulas

Occasional Contributor

Hi!

I've been utilizing LAMBDA custom formulas, and one issue I've encountered is anytime I make a new sheet, or duplicate one, even if the Scope is set to "Workbook", Excel automatically creates a duplicate of the LAMBDA functions with the Scope set to the new sheet. I'm not sure why, and wondering how to stop this. Thank you!

12 Replies

@Dylan1G 

I haven't seen any such behaviour.  The workbook-scoped names defining Lambda functions remain unaltered whilst the Range references spawn sheet-local copies.

Perhaps a more detailed example that we can replicate would be in order.

I've encountered similar quirks here and there recently with the Name Manager. A few days ago, a dynamic named range (Created with TAKE/COUNTA) had reverted to a static range. I know had defined it because the text of the formula was still on my clipboard. With Lambda, I've had 1 issue where a function I had created needed to be updated. I updated it through name manager and then proceeded to call it in a formula. I received an error then checked the Name manager to find the Lambda update had not been saved (In fact, this quirk happened 3 more times!). My solution was to delete the Lambda and re-add it.

Perhaps these quirks are due to an environment in which Advanced Formula Environment exists alongside Name manager?

Was thinking it’s possible it’s because I specified a range in one of the formulas without specifying the sheet. However, I don’t think I did that anywhere. This is a very frustrating bug, assuming it is one and seems to be affecting other aspects too.

Just checked with your sheet. It does occur too. It doesn't happen when I add a sheet, but when I duplicate one. Make a copy of any of the sheets and then check the name manager. All of the lambda formulas get duplicated with the scope defined to the new sheet.
@Peter Bartholomew

@Dylan1G 

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.

Do you happen to run Advanced Formula Environment? That's the only cause I can think of at the moment. This bug is odd because the Lambdas are stored as named items in Workbook.xml which is not copied when a sheet is copied. Another Sheet[#].xml file is created in the zip package. The sheet xml files contain constants and formulas but no defined names.
I do not, I just make them as normal.
Bizarre! I was thinking the same, but realized all of my formulas specified tables. Also, getting different results doing the same exact thing of you is super strange. Anyone I should reach out to?

@Dylan1G 

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.

 

 

@Peter Bartholomew thank you for that. However, with yours that include no table references, the same thing occurs.

@Dylan1G 

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.

 

 

@Peter Bartholomew Thank you. At this point, I think it's safe to say there is some sort of error is occurring. How would I send a copy to Microsoft?