I’m having an issue with the Statements template from Tiller Labs.
I know there are a lot of formulas in the spreadsheet which can limit the ability to perform some standard spreadsheet actions.
I am using the ‘Statements’ sheet. Because I did not add all of my current year statements in order, not all rows are in sequence. If I simply sort the range by column A, the arrayformula() function introduces errors.
Ex - Before sorting:
After Sorting on column A:
Note the blanks for the first two statements.
Simply copying the formula to the empty cells creates a #REF! error.
Looking at the cell contents before and after sorting, maybe I am missing something else, but I do not know what. Maybe just not getting the whole range since columns after L are hidden.
In any event, there should be an easier way to get the statement list in date order.
Statement Name formula: =arrayformula(if(isblank($C3:$C)+isblank($D3:$D),iferror(1/0),text($C3:$C,$A$2)&iferror("-"&RIGHT(vlookup($D3:$D,{INDIRECT($V$4),INDIRECT(vlookup(B2,{$T$3:$T$7,$V$3:$V$7},2,false))}, 2, FALSE),4),"-"&LEFT(SUBSTITUTE($D3:$D, " ", ""),10))))
I ran into the same issue. Instead of fighting it, I ended up unhiding the hidden columns and found the “Statement Names” column (N on my sheet) shows them all sorted in descending order (this is probably where the data validation pulls from), which can be useful if I’m trying to see if anything is missing.
Frankly, the Statement names can be arbitrary— anything you want— so long as they match to the Transactions sheet. The ARRAYFORMULA() is just designed with a handy recommendation, but you don’t need to use it… i.e. you could delete the ARRAYFORMULA() and direct-enter your own statement names or you could modify the ARRAYFORMULA() format.
If you go with a different approach, just be sure to find/replace existing transactions in the Transactions sheet.
It sounds like the sort operation is moving the ARRAYFORMULA() from the first row. I think it should work for you if you copy the formula before the sort operation, then perform the sort, then paste the formula from the clipboard into the first row (i.e. where it was pre-sort), and finally delete the second/original instance of the ARRAYFORMULA() that was sorted into a new location (further down).
