Copy Sheet Creates Copies of Lambda Functions in Name Manager

[MikeMcCollister]

I have recently discovered the filter function in Excel. What a wonderful edition! Because of this, I am working to remove VBA from my budget spreadsheet. I'm also creating some Lambda functions using the name manager for my new functions. All my Lambda functions use the Workbook scope. When I copy an existing sheet, like January so that I can rename it to February, all the Lambda functions are duplicated and are scoped to the newly copied sheet. Any ideas how to prevent this from happening?

Thanks,

Mike

 

[Dave Patton]

Excel does not always duplicate the Lambda formula.
How are you producing the new sheet?
Can you post an example of the formula?
N.B. You can post a concise example with the forum's tool named XL2BB.

 

[Fluff]

Do you have any ranges hard-coded into the function?

 

[MikeMcCollister]

I'll try this without using XL2BB as this is a multi-tab spreadsheet.

First sheet is called "January" and totals up each category from the second sheet "Checking". Note that B1 contains the month number for the month name in A1. I have a Lambda function in the named CategoryTotalForMonth. The Lambda function is as follows:

=LAMBDA(month_num,category, SUM(FILTER(Checking[Amount],(Checking[Category]=category)*(Checking[Month]=month_num))))

This function has the scope of Workbook.

When I copy the January sheet (using ctrl-drag) and rename to February (also change A1 to February) I see that CategoryTotalForMonth has a duplicate in the Name Manager with a scope of "February".

I just don't want global lambda functions to be duplicated.

Thanks,

Mike

 

[MikeMcCollister]

Do you have any ranges hard-coded into the function?

Yes, my lambda function references a workbook-scoped table name.

Mike

 

[Fluff]

I think that's the reason. You will probably need to pass those ranges to the function in the same way you pass month_num & category.

 

[MikeMcCollister]

I think that's the reason. You will probably need to pass those ranges to the function in the same way you pass month_num & category.

Fluff,

Thanks. That is unfortunate. In my actual document, I am referencing multiple columns. I was trying to make the formulas look much cleaner.

I guess that each time I copy a month sheet I can prune the newly created names.

Mike