Preview of Dynamic Arrays in Excel

Sameer_Bhide , Excel has few hundred functions developed by different people at different time. I don't think exact list of functions, which support auto-lifting with DA, exists. But you always safe to use =func(+reference, parameters). "+" works both with texts and numbers.

Sameer_Bhide 

I believe there is a video from ExcelIsFun or MrExcel which contains a list of such functions.

But you have to search for yourself.

There is question of parameter tüpe in design. Some functions don't support range but only value
This workaround is quite simple and quite common

same had you been used in TABLES where you use [ColumnName] as parameter. at beginning [ColumnName] didn't need to be marked as current row [@ColumnName] and this time it was mandatory to use similar workarounds

There is no list about "bad designed" functions - so please test-and-try

Henn

Henn Sarv , @Detlef Lewin

Thanks - It works - but why is this workaround needed ?

Also is there a list of other Functions that don't support the Spilt range reference (#) directly

Cheers

Sam

Sameer_Bhide try =WEEKNUM(A2#+0,2) - it works

Sameer_Bhide 

Did you try this?

WEEKNUM(-#A2,2)

JoeMcDaid 

The # reference is not supported in WEEKNUM

For e.g. WEEKNUM(#A2,2) -gives #VALUE - however WEEKNUM(A2:A10,2) does return a spilt array

Cheers

Sam

Henn Sarv , since that's array of arrays which is not supported in Excel natively. Another workaround could be

splitInColumn=
LAMBDA(array, [sep], [pad_with],
    LET(
        separator, IF(ISOMITTED(sep), ",", sep),
        pad, IF(ISOMITTED(pad_with), "", pad_with),
        IFNA(
            DROP(
                REDUCE(
                    "",
                    array,
                    LAMBDA(a, v,
                        VSTACK(a, TEXTSPLIT(v, separator))
                    )
                ),
                1
            ),
            pad
        )
    )
);

Hi people,

Just find that 14 new DA functions are coming. SUPER!
I just imaging how did I work without them

=STACK - unioning Arrays
=TAKE - much better than using INDEXES for selection part of DA
=ROWS and =COLUMNS - this same

=TOROW and TOCOL - SUPER!

and last but not least 🙂

=TEXTSPLIT, =TEXTBEFORE, =TEXTAFTER - great - I can now forget my function add ins (=HLINK is something I need from MyFunctions)

=TEXTSPLIT I find strange behaving

I have column (excerpt from table) fith names separated by commas (Henn, Ants, Peeter) - and several rows with different set of names in each

On one cell  (C2)  =TEXTSPLIT( works fine, but on DA (C2#) stop work - producing ONLY one column (1st names from each)

Fortunately I find VERY FANCY workaround TEXTSPLIT(TEXTJOIN(";";TRUE;C2#);",";";";TRUE;"") producing exact the result I need

so with following formula I get what I need to get:

=LET(
m;TableX[Names];
s;TEXTSPLIT(TEXTJOIN(";";TRUE;m);",";";";TRUE;"");
c;UNIQUE(TRIM(TOCOL(IF(s="";0/0;s);3)));

c)

NB! Excuse againg - I need to use semicolons rather than commas in formulas

AHA! My question - Why that TEXTSPLIT dont work on DA-s

https://techcommunity.microsoft.com/t5/excel/join-the-results-of-two-filtered-tables-in-a-single-range/m-p/2466064#M104006