I have a lot of saved filter views for my transactions tab, but I notice that in those views, it doesn’t always get the date sorted correctly. There might be 6/4/21 at the top and then a random 6/7/21 a few rows.
So I tried to save a new filter view where I simply sorted all transactions by date, but for some reason the “Group” Column gets messed up when I do this filter and doesn’t display group and shows an error message. The group column also messes up when I sort the amount column.
This is the error that shows in the Group Column:
Error Did you mean I$1:I? In a sorted filter view, range references must refer to the same row, or be absolute ($ before row numbers).
Also FWIW, I have changed the order of the columns in the transactions tab… not sure if thats a problem (hope not!). Here is a screen shot for formula and column headers.
I haven’t made use of the Filter Views feature, so I don’t know much about that. The error you get in the Group column, I often have that happen on arrayformulas when data gets into one of the cells in the column, so the arrayformula can’t fill the way it wants to. Not sure how a filter view could move data into that column, but something to check for. Sorts that get wonky usually for me have been related to an empty row or column in the table, so the sort isn’t sure the extents of the table, and ends up only sorting part of it. Don’t know if either of these examples apply, but probably worth checking out.
Thanks for the tip! I’ll look into that.
Based on the error message you’re seeing I’d guess it has something to do with the Filter View you’re using. I’ve never seen this before and haven’t experimented with filtered views much. Let us know how it goes.
So I think @jpfieber was onto something. The thing with filter views is that you have to update the range of the filter after you fill sheets. Once I updated the range to include all the new transactions then the problem went away in most cases.
However, it also seems like if I I am looking at a filter view, say “show all items that are blank category”, and then try to sort by date or by amount then I get the error again. I’m thinking that it might not be possible to do a sort on a preset filter. I think you might have to just do a filter each time. First sort the date / amount, then filter out whatever you need.
2 Likes
I’m having the same problem - the Group array formula breaks when any sort is applied in a filter view Simple repro steps:
- Go to transactions, add the group column. All good here.
- Data/Filter View/Create filter view.
- Click any column, and choose sort ascending or descending.
Error Did you mean D$1:D? In a sorted filter view, range references must refer to the same row, or be absolute ($ before row numbers).
Something in the way the group array formula is constructed breaks when using any kind of a sort on a filter view - of any column. There are workarounds, but I don’t know enough about this formula to rework it in the template. WHY DON'T FILTER VIEWS AND FORMULAS WORK ? - Google Docs Editors Community
If it’s complaining about column D, I’d try changing every occurrence of D1:D or D$1:D in the Group column formula to $D$1:$D
The $ symbol is like a lock, so where dragging a formula that contains D1:D left or right will allow the letter and number to change each cell it’s moved to, adding the $ symbol locks the letter or number so if the formula is repeated, it doesn’t change. In this case, we likely don’t want that range to change regardless of which cell it’s in, so setting it to $D$1:$D tells it to keep that same range no matter where this formula appears.
That fixed it. How do I get it rolled back into the main fork of the template? Hate to lose the edit on the next version. Here it is:
=arrayformula(if(row($D$1:$D)=1,“Group”,iferror(vlookup($D$1:$D,{indirect(“Categories!$”&SUBSTITUTE(ADDRESS(1,MATCH(“Category”,Categories!$1:$1,0),4),1,“”)&“$2:$”&SUBSTITUTE(ADDRESS(1,MATCH(“Category”,Categories!$1:$1,0),4),1,“”)),indirect(“Categories!$”&SUBSTITUTE(ADDRESS(1,MATCH(“Group”,Categories!$1:$1,0),4),1,“”)&“$2:$”&SUBSTITUTE(ADDRESS(1,MATCH(“Group”,Categories!$1:$1,0),4),1,“”))},2,FALSE),“”)))
If this is a Group column you manually added to your Transactions sheet, then you don’t have to worry, nothing will try to change that. I don’t think there are any workflows that will try to change that, a smart workflow would check if it exists, and if so, don’t do anything.
Right - forgot I had added it manually based on a blog post by Heather. I’ll ask her to update her post. Add a Group column to the Transactions Sheet | Tiller Help Center
2 Likes
