Excel Dynamic Arrays
1 Topic
Dynamic Array formula not backward compatible
Hello. I have the Excel Insider edition and have used the new Dynamic Array formulas as follows: =SORT(FILTER(CHOOSE({1,2,3},t_Main[Rank],t_Main[Field 2],t_Main[Total]),t_Main[Rank]<(J3+1)),1) This function works fine for me, and it SPILLs correctly as I would like. I have passed my worksheet on to a colleague who does not have Insider Edition (he has the "normal" 365 channel). My understanding was that you would need Insider Edition to use the new Dynamic Array formulas, but that previous versions of Excel would be backward compatible and would be able to use the resulting spreadsheets without changing the formulas. This seemed to be the case for my colleague, as the formula was converted to something else on his non-Insider Excel: =_xlfn.UNIQUE(_xlfn._xlws.SORT(_xlfn._xlws.FILTER(CHOOSE({1,2,3},t_Main[Rank],t_Main[Field 2],t_Main[Total]),t_Main[Rank]<(J3+1)),1)) He can see the results of the spilled formula without a problem HOWEVER ... if he changes any of the data that causes the SPILLed results to change, he gets a #NAME error rather than the new formula result. So, my question is: Is it right that previous versions of Excel (particularly 365) should be backward compatible for the ARRAY formulas? Is this the expected behavior, an issue, or a potential setup problem on the other machine? Any input appreciated... thanks BrianGGGSolved6.7KViews0likes12Comments