Is it time to revisit the Defined Name? I believe 365 requires a renaissance of the Name.

lori_m PeterBartholomew1 I wasn't able to fully hash out why I was getting the two entries, but I worked around it by deleting the Workbook scope entries manually.

Some highlights:

• defineIt, a wrapper for DEFINE that hard codes everything but comment and scope
• nameDtl, a wrapper for DEFINITIONS that provides some dynamic filtering
• pseudo-Sheet Defined Function (SimplePivot tab): everything was defined using defineIt with local scope and step-by-step detail for building the pivot function is presented.
• pivot, a rudimentary pivot table LAMBDA function
• summarize, built to extract a subprocess of the original pivot algorithm shown on SimplePivot, and to resemble DAX function of same name
• fair amount of uses of thunks
• aggHelper can take custom LAMBDAs, so you can actually get much more specific aggregations that would require a data model and DAX
• some other helper functions

Need to polish this a bit, but its all in working order currently, tell me if you notice something I missed or is not clear.

tboulden 

Thanks for looking into this. I think the scope parameter is designed to input either sheet name or workbook so one can specify any sheet scope. I can't fully test but i was hoping something like this would work

=LET(√,LAMBDA(x,SQRT(x)),DEFINE("Sheet1",TRUE,"function","LAMBDA","Square Root"))

In my version the scope is shown as 'Sheet1' and it looks like a function category is created for lambda but i don't see the function listed there yet. I've got other commitments at present, but plan to come back to this in a month or so.. Any help with bug fixes / improvements much appreciated in the meantime.

lori_m  So far, so good; I made one successful modification to the Parse function to accomodate {,} for arrays as names. I'm struggling a bit with my other modification. I couldn't get the scope variable to register anything but "Workbook", so I Dim'ed a var ws for Worksheet and modified setNames to this:

        If IsEmpty(A(eScope, i)) Or A(eScope, i) = "Workbook" Then
            Set o = wbk
        ElseIf A(eScope, i) = "Local" Then
            Set o = ws
        End If
        

This is working, but it is adding two entries: 1 with the local sheet scope and 1 with "Workbook" scope. I've tried figuring out where its doubling up, but not having success. Any ideas? Once I get this working, I've got a LAMBDA-based pivot table function I'll showcase here as well.

lori_m 

Sorry I have been somewhat unresponsive. My Surface dock decided it no longer wanted to talk to the monitors and I am not happy at the prospect of trying to chair conference sessions with just a laptop screen. I think it is software repair/update that is required because another machine still drives the setup correctly.

Back to Excel, I have downloaded the Add-in and will try it as soon as I get sorted.  On the Lambda front, both the formula bar and name manager fall woefully short of requirements, so I would like to see something based upon Jack's editor in the role of "the Excel formula editor" and ultimately have it apply to worksheet formulas, defined names, conditional formats, validation formulas and the chart engine (is that all?).  Actually the last three are probably best generated through the use of defined Names even now.

PeterBartholomew1 

Combining name definitions with a floating/dockable formula bar would seem ideal. A couple of related possibilities that come to mind are updating named formulas via
- the 'Name Box' next to a formula (instead of using it to define a range name)
- a 'Names Pane' similar to Selection Pane (alt+f10) which is editable from the formula bar

In effect a name could become a valid selection distinct from any cell reference having its own formula. I do have doubts, however, whether this kind of thing could be implemented as a third party solution due to the lack of extensibility around the formula bar.

On the other hand, the LET approach does have some advantages by keeping everything in one location on the worksheet, allowing multiple names to be updated at once and also being able to incorporate testing and updating into a single action. Due to the validation, I find the names list may sometimes need to be manually refreshed using F9, Charles Williams has a useful tip too - clicking away rather than hitting enter after a formula update changes recalculation order with data validation (http://www.decisionmodels.com/calcsecretsc.htm)

RE GitHub: these steps seemed to work ok for me.
1. From the previous Github link click download and copy the file (ctrl+C).
2. Go to Developer Tab > Excel Add-ins then click browse and paste the xlam file (ctrl+V)
3. Click to add the file as an add-in (always check code before enabling macros!)

4. Excel Options > Trust Center > Trusted Locations > Add the Add-ins folder location

It turned out the issues i was experiencing before were due to not having done #4.

lori_m 

That looks truly amazing.  Now I have to learn how to use GitHub!  It also seems to be at the centre of the MSR approach to sharing Lambdas so I guess it will be time well spent. 

From their presentation, it also appears that the Lambda editor has moved on since the time I downloaded it.  The reservations about the Lambda editor that I still have is that it takes one away from the spreadsheet and into a code environment for development and the auditor is forced to follow.

I think I would prefer it if Jack's Lambda editor were primarily a replacement for the Formula Bar rather than Name Manager.  The idea is then that the scope of the name is controlled by the LET formula so everything is visible and auditable from the spreadsheet.

tboulden 

Great, glad you managed to get it working. On my setup I just noticed that I need to uncheck and recheck the addin to register the two DEFINE and DEFINITIONS functions after launching Excel, this can be done from the developer tab as below. I'll try and add an update for that next week. Please let me know of any other issues...

I wanted to also check if LAMBDA work as expected like in the xlsx attachment.