Microsoft=SEQUENCE(n) is very nice new function
till today I used for this following =ROW(OFFSET($A$1;0;0;n;1)) when needed. One example:
=IF(ISERROR(TEXT((CODE(MID("FEDCA@";LEFT(A11;1);1))-50)*1000000+LEFT(A11;7);"0000\.00\.00")+0);"isikukood vigane";IF(IF(MOD(SUMPRODUCT((MID(A11;COLUMN($A$1:$J$1);1)+0);(MID("1234567891";COLUMN($A$1:$J$1);1)+0));11)=10;MOD(MOD(SUMPRODUCT((MID(A11;COLUMN($A$1:$J$1);1)+0);(MID("3456789123";COLUMN($A$1:$J$1);1)+0));11);10);MOD(SUMPRODUCT((MID(A11;COLUMN($A$1:$J$1);1)+0);(MID("1234567891";COLUMN($A$1:$J$1);1)+0));11))=MID(A11;11;1)+0;"isikukood õige";"kontrolljärk vale"))
this is to validate Estonian Personal ID - now I can rewrite this to shorter.
One question too:
Is there any idea or plan to allow dynamic arrow formulas to be used in TABLES. In same table (like =SORT) or on another table (like =UNIQUE)
for example - currently I have to "sort" the names on table column use following practice:
1. add column RANK:=
|
=COUNTIF([Name];"<"&[@Name])+1 |
2. then add column SortedName:=
|
=INDEX([Name];MATCH(ROW()-ROW(Table1[[#Headers];[SortedName]]);[Rank];0)) |
Dynamic arrays will help this a lot
