Microsoft
Microsoft"[I am] not sure we know or may agree what the perfect is"
I am sure that is correct! There are also differences arising from the use of an external prototyping environment against what could be achieved with integrated functionality.
"megabytes of such names duplicate in AFE"
I would consider any duplication beyond working data specific to the task in hand to be undesirable as a long-term solution. My immediate feeling was that as a Name Manager replacement the AFE should provide a management overview of all the available names but have only a single name open for editing. As a formula bar replacement, the worksheet itself provides the overview and the active cell is the only one visible for editing.
I suggest the two environments should be combined simply because I have found the pattern of solution development has changed; the use of 10+ line formulas now offers the clearest and most transparent route to specifying the calculation I wish to perform. Whether the LET or LAMBDA function is then loaded as a defined name or worksheet cell may change during the course of the formula development and should not require a change of development environment. Ultimately, I would like to see all formula definitions addressed, the other obvious examples being conditional formatting and validation.
Something that would be far more ambitious would be to support test evaluation of the formula as it is being developed, so replacing the use of F9 in the formula bar and Evaluate Formula dialogue box. I believe 365 online evaluates new formulas client side before the server calculation is triggered to recalculate the workbook so "if you were to commit this modified formula at the current location this is what you would get" calculation would appear to be possible. At present, to check a variable from an extensive LET formula requires copy/paste to return the the value from the LET and the modified formula has to be saved so that it evaluates on the worksheet.
I am conscious that I may be seen as an "ungrateful bloody user" so I would like to reiterate the thought that what we have now is amazing and beyond my wildest expectations when I was badgering Microsoft for a complete rework of array formulas, which, at the time, had all the user-friendliness of a cornered rat.
