Dermot

Copying Lambdas between workbooks and keeping them updated

Overview Discussion
Dermot

Dermot

Board Regular
Joined
Aug 11, 2006
Messages
199
Office Version
  1. 365
Platform
  1. Windows
Dermot submitted a new Excel article:

Copying Lambdas between workbooks and keeping them updated - Copying Lambdas between workbooks and keeping them updated

Lambdas are held in individual workbooks. How can we copy them between workbooks, and keep them up to date if they change?

Based on the current Excel beta version 2111
* copying any sheet (even blank) from workbook A containing lambdas to another workbook B, will also copy those lambdas to B. The copied sheet can be deleted immediately after copying, ie it does not need to be retained in B.
* if the lambdas already exist in B, Excel will duplicate them, ie the Name Manager will list...
Click to expand...

Read more about this Excel article...
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Andy Gordon and Jack Williams from Microsoft Research revealed that the LAMBDA editor add-in they've been working on should be available via the Microsoft Store sometime in the near future. Video should be available here, video should be available under the "ACCESS" section, first session, around hour 10 of the Teams recording. I've gotten to test an earlier version, but what they demo'ed today has syncing with GitHub, which would be helpful in managing LAMBDA versioning.
 
I have a macro in my PERSONAL workbook that adds my LAMBDA functions, but it's much more simple. First, every LAMBDA function starts with an underscore (_). Each function uses 3 lines of code:

ActiveWorkbook.Names.Add Name:="_LambdaFunctionName", RefersToR1C1:= _
"=LAMBDA(LambdaParameters and Functions Here)"
ActiveWorkbook.Names("_LambdaFunctionName").Comment = "Defined Name Comment Here"

The nice thing is that if a change is needed to a function, it can be done in the VBA and then updated as needed as existing functions will be overwritten with the updated version.
 
You must log in or register to reply here.

Forum statistics

Threads
1,216,503
Messages
6,131,022
Members
449,616
Latest member
PsychoCube

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top