Excel Dynamic Array Improvements

SWill856 - this is a HUGE change to the Excel calc engine. I'd rather they take their time to get it right than rush it out based on an arbitrary date.

Will the new builds automatically find and replace any existing SINGLE() functions with @ as files are opened? I see SINGLE() is still available as a function in current insider builds. Will it be removed?

JoeMcDaid - in the example worksheet I sent you last week, this build is still "corrupting" some formulas. I restored that file to a pre-Dynamic Array version of the worksheet to start clean and opened on the Monthly Targeted build. So far so good.

=IFERROR(
    IFS(
        ISBLANK([@[Item No.]]),"",
        COUNTIFS([Bin],[@Bin],[Item No.],[@[Item No.]])>1,"Error - Duplicate Bin/Item Combo",
        NOT(ISBLANK([@[Item No.]])),INDEX(tblItemMaster[item_desc_1],MATCH([@[Item No.]],tblItemMaster[item_no],0))
    ),"Error - Invalid Item Number - Check Tag")

Became below when I opened it in the Insider build.

=IFERROR(@
    IFS(
        ISBLANK([@[Item No.]]),"",
        COUNTIFS([Bin],[@Bin],[Item No.],[@[Item No.]])>1,"Error - Duplicate Bin/Item Combo",
        NOT(ISBLANK([@[Item No.]])),INDEX(tblItemMaster[item_desc_1],MATCH([@[Item No.]],tblItemMaster[item_no],0))
    ),"Error - Invalid Item Number - Check Tag")

with the @ added on the first row. Saving, closing, and opening that in a Monthly Targeted build worked ok. The @ got removed.

I then edited on this weeks Insider build, adding the &"test" to the last row and saved/closed it.

=IFERROR(@
    IFS(
        ISBLANK([@[Item No.]]),"",
        COUNTIFS([Bin],[@Bin],[Item No.],[@[Item No.]])>1,"Error - Duplicate Bin/Item Combo",
        NOT(ISBLANK([@[Item No.]])),INDEX(tblItemMaster[item_desc_1],MATCH([@[Item No.]],tblItemMaster[item_no],0))
    )&"test","Error - Invalid Item Number - Check Tag")

When I opened it on a Monthly Targeted build, 1902, build 11328.20070 it did the _xlfn.SINGLE() thing again.

=IFERROR(_xlfn.SINGLE(
    IFS(
        ISBLANK([@[Item No.]]),"",
        COUNTIFS([Bin],[@Bin],[Item No.],[@[Item No.]])>1,"Error - Duplicate Bin/Item Combo",
        NOT(ISBLANK([@[Item No.]])),INDEX(tblItemMaster[item_desc_1],MATCH([@[Item No.]],tblItemMaster[item_no],0))
    ))&"test","Error - Invalid Item Number - Check Tag")

Now the entire cell is useless on anything but an Insider build. Now to start over.

So I cleaned all of that up and got it as the function at the top again, then I decided to open it on both PCs at the same time - hosted in Sharepoint online.

I first edited the function on the Monthly Targeted (MT) build and both MT and Insider showed the formula as the first one above - no @, no SINGLE(), no nothing. Then after I confirmed the MT edit was recognized by the Insider build I edited it on the Insider build, tweaking my "test" text. That caused the MT build to say the file had been modified but it couldn't open it, so I needed to either Save As, or discard and reopen. I chose the latter, and the MT build then showed this CSE function. ¯\_(ツ)_/¯

={IFERROR(
    IFS(
        ISBLANK([@[Item No.]]),"",
        COUNTIFS([Bin],[@Bin],[Item No.],[@[Item No.]])>1,"Error - Duplicate Bin/Item Combo",
        NOT(ISBLANK([@[Item No.]])),INDEX(tblItemMaster[item_desc_1],MATCH([@[Item No.]],tblItemMaster[item_no],0))
    )&"test","Error - Invalid Item Number - Check Tag")}