Rack of Lambda
There’s been a lot of content shared recently to commemorate the upcoming 40th anniversary of Microsoft Excel. Personally, I’ve only been using Excel for around half that time, but October also marks my 2-year anniversary since "joining the conversation" on this forum. As a gift from me to you (anyone interested), I’ve spent some time over the past few weeks revisiting old posts, updating methods I shared previously and packaging them into a collection of generalized Lambda functions to assist with a variety of common array manipulation and transformation scenarios. The attached file contains some 35+ Lambda functions, ranging from very simple concepts to much more advanced techniques. You can also import them directly from my gist, if desired. While they were all compiled and composed of my own accord, I would be remiss if I failed to credit the community and its members as a major resource in my own development. The amount of knowledge, tips and tricks gained through community collaboration is simply invaluable. You may notice some recurring themes in the way I’ve written many of the functions. For example, I like to keep the optional arguments as simple as possible, using either Boolean values passed to IF, or numeric options from 0 to 3 passed to CHOOSE. Also, many of the array transformation functions use TOCOL-IF-SEQUENCE in one way or another, with MOD-QUOTIENT-SEQUENCE used only a few times in the more complex algorithms (e.g. HWRAP and VWRAP). The collection also includes a few examples of Lambda recursion, the most notable being PF (Prime Factorization). CROSSJOINM was written as a "how-to" demonstration for filtering multiple optional arguments using LAMBDA and NOT-ISOMITTED. There’re also some powerful scanning functions like SCAN3, which can handle multiple input arrays, as well as EVALS with VALS2 to VALS7, which can store and recall multiple variables at each iteration (useful for corkscrew calculations). What you won’t find, however, are methods that use INDEX in an iterative manner with functions like MAKEARRAY, SCAN, etc. as these are only efficient when iterating over a range reference (they will bog down considerably and become practically unusable after just a few thousand iterations when looping over an array object). As such, I don’t recommend them as "generalized" solutions, although they can be very effective on a case-by-case basis. Similarly, you will only find 2 examples in this collection that use REDUCE-STACK in a limited capacity, with fewer than 10 iterations, as I also consider this to be a method of last resort due to its problems with efficiency when the number of iterations cannot be controlled. Hopefully one or two of them proves useful. If not, no big deal. Many of the examples in the attached file are interactive, so you can see how the different options affect the output. For those brave enough, please feel free to share your own custom functions too. I’d love to see what you got. Cheers!
Custom Fields in Booking not showing in calendar invites
I have added Custom Fields in the Booking configuration, see below. But those are not showing in the calendar invite but only in a separate confirmation email. Would it be possible to see the answers for those customs fields in the calendar invite?
Two lists of transactions. Want to create one large 'combined' one.
I have a workbook which I use for tracking transactions across two bank accounts. Each account has a separate 'ledger' sheet which lists all the incomings and outgoings. I want to create a third sheet which shows the entries of both accounts in one combined ledger. I would like it to be sorted by date and to have one column to indicate which account the entry is coming from. This is a link to a a dummy workbook with three sheets: Combined, Account 1 and Account 2 so you can see what I mean. https://1drv.ms/x/c/eea13e24843cdffd/EdNz2TLk0hJEueH-abYJF6ABxp69AS5eU0NqCOG4vlZAXQ?e=iYZ2Do If this is possible, please could someone with better skills than me please advise! Thanks!
Working with workbooks shared via Teams/Sharepoint
Hello Excellers, I need some insight on an issue that I am not sure what the source is... A) We sometimes share workbooks via Teams. You know when you are in a particular chat and next to the name of the chat at the top of the screen you see Shared and then you see Files button a bit below the Shared menu and when you click on that Files button you will see a list of what workbooks are shared. B) So I wrote some VBA code to download a copy to the Downloads folder like that: Dim RetVal As Long Dim SharePointFileURL As String Dim LocalDownloadPath As String Dim FileName As String RetVal = URLDownloadToFile(0, SharePointFileURL, LocalDownloadPath & FileName, 0, 0) If RetVal = 0 Then MsgBox "File successfully downloaded to: " & LocalDownloadPath & FileName, vbInformation This will message be replaced by the code we need to run... but for now I needed an indicator that it finished downloading. Else MsgBox "Failed to download the file. Please check the URL or your network connection.", vbExclamation End If So RetVal is = 0, and I do see the workbook in the \Downloads folder and it has a size of 4KB and the correct Date Modified time stamp, BUT When I try to open the workbook via the Excel desktop application I get the message: Excel cannot open the file "File name here" because the file format or file extension is not valid. Verify that the file is not corrupted and that the file extension matches the format of the file. The workbook should be 34KB in size and that 4KB file is not what I need??? Any ideas how to manage this, saving a workbook from a SharePoint / Teams file location to the computer so that we can run VBA code on it. GiGi
Finding Possible Matches to a Solution
Hello Everyone, I'm wondering if anyone has any tricks or formulas that I can use to find any possible combination of numbers that will create the desired solutions I'm looking for. Take a look at the picture I uploaded for reference. If I have a list of data in column A, I will be looking for a given set of numbers that will create a sum that matches column C. I'm looking for a trick or formula that will return all possible combination of numbers that will sum up to equal Column C and then return those possible combinations in another column for me to see (for example like what you see in columns E, F, & G). Does anyone have any ideas on how or if this can be done? I would like to do this without the use of complicated macros or other codes if possible.
Compile Error
I regularly create CPRs using Excel. This has always worked just fine, then the other day it suddenly stopped working and Excel gave me the following error message: "Compile error in hidden module: cpInterview. This error commonly occurs when code is incompatible with the version, platform, or architecture of this application. Click 'Help' for information on how to correct this error." I did click "Help" and a brief article popped up that did not help me resolve this issue. I don't understand why my report worked one day and then suddenly didn't work the next day. That makes no sense to me. I contacted Microsoft and they were completely useless, said this wasn't in their scope and there was nobody who could help me from their end. That also makes no sense to me since this is a Microsoft issue, but they told me to post in this online forum to try to seek help from other users. Does anybody know why this error suddenly happened and, more importantly, how to fix it??? I heavily rely on the CPR function to do my job and this is causing me a lot of hardship. I have Microsoft Office Home 2024 running on a Windows 11 HP Laptop. Thank you!Utilizing Excel's turing capabilities to create Conway's 'Game of Life'
The Background It's been said with Lambda (and LET and a wealth of functions in 365) Excel has become 'turing-complete'. To quote the article linked below: "You can now, in principle, write any computation in the Excel formula language." https://www.microsoft.com/en-us/research/blog/lambda-the-ultimatae-excel-worksheet-function/ The Challenge I thought it would be fun to create Conway's 'Game of Life' in Excel 365 to see how far I could push things. Conway's Game of Life - Wikipedia The rules are simple: A 'cell' has up to 8 adjacent cells (less if the cell is on the edge of the board). A 'neighbor' is a cell with a 1 while a 'dead' cell is empty. An 18x18 board Multiple iterations Bigger boards! more (it's relaxing to create new shapes and designs) The Approach My first thought was to use MAKEARRAY because I could use 'r' and 'c' coordinates and there would be no stacking. I devised a recursive function that worked for 1 iteration but failed on subsequent iterations because the use of TAKE/DROP was slowly shrinking the board! The revised approach is essentialy a recursive MAP that uses 3 arrays: the input matrix, the 'r' array (row numbers) and the 'c' array (column numbers). It's my way of using r/c without using MAKEARRAY. For Discussion I welcome any improvements to the existing function and any different approaches someone may have to creating Conway's Game of Life. Conway Lambda follows: Conway =LAMBDA(matrix, iterations, IF( iterations = 0, matrix, Conway( LET( height, ROWS(matrix), width, COLUMNS(matrix), r_arr, SEQUENCE(height) * SEQUENCE(, width, 1, 0), c_arr, SEQUENCE(height, , 1, 0) * SEQUENCE(, width), CheckNeighbors, LAMBDA(lattice, r, c, LET( RCx, LAMBDA(row, col, IFERROR(CHOOSECOLS(CHOOSEROWS(matrix, row), col), 0) ), N, RCx(r - 1, c), NE, RCx(r - 1, c + 1), E, RCx(r, c + 1), SE, RCx(r + 1, c + 1), S, RCx(r + 1, c), SW, RCx(r + 1, c - 1), W, RCx(r, c - 1), NW, RCx(r - 1, c - 1), compass, VSTACK(N, NE, E, SE, S, SW, W, NW), neighbors, SUM(compass), IF( AND(lattice = 0, neighbors = 3), 1, IF( AND(lattice = 1, OR(neighbors = 2, neighbors = 3)), 1, 0 ) ) ) ), MAP(matrix, r_arr, c_arr, CheckNeighbors) ), iterations - 1 ) ) )find where named range is used/referenced in entire workbook
Hi! I inherited a complex workbook used to run general ledger and reports for a small company. There are many named ranges used for lookup tables as well as pivot tables. In the Name Manager, I can see the named ranges and their values. How can I determine where a named range is being used? I am looking to delete old named ranges refencing worksheets that have since been deleted. For example, a named range from ledger was used to identify data for 2021. That named range was used in a pivot table for 2021. Now that I have deleted the pivot table for 2021, it is a named range that is not being used. Before I delete the named range, how can I ensure that no other worksheet is using that named range (no just assuming it was only used in the typical place - might be referenced in another report worksheet)? Additionally, when I look at named ranges in the Name Manager, I can see that several have #REF! for Value (instead of a column header listing). They also have "#REF!" in the Refers To column (ex: 'MY WORKSHEET'!#REF!). That indicates that the range is invalid, correct (like the source data worksheet was deleted or the data no longer exists in the original range)? If so, it is useless, but before I delete the named range, I want to ensure it is not being referenced in any other worksheet (see question above). If it is being referenced in a worksheet, then I need to refactor that worksheet or it may be something I can delete as well.As any one found cool icons to use on a Custom Ribbon?
Hello Excellers, I just finished making a neat custom ribbon for an application, and I am wondering if anyone has found a cool and neat place to grab some icons for the button faces specially if in color. My ribbon looks nice, and most importantly it works as intended, but I am kind of thinking it could be more colorful. So far I only used the built-in stuff. Thanks for any hints. GiGi
