What is the goal of your workflow? What problem does it solve, or how does it help you?
This makes adding tags very easy, and keeps your tags consistent.
Please describe your workflow. What are the sheets? Does it use any custom scripts or formulas?
The Steps:
create up to four columns in the Transaction sheet, and in Row A1, at columns P,Q,R, and S, enter your desired tags.
this example assumes that you’re entering your data in row 181. This was just a random row that I picked, but it’s mentioned in the formula, so I thought I’d make that clear.
…now when you make a check in the “Flagged” column for example, “Flagged” will automatically appear in the Tags column. It takes a moment, but it works, and supports multiple tags.
Anything else you’d like people to know?
I believe this solution is restricted to 4 columns, as issues with the sheet began when we tried 5 columns.
You’ll need to copy the Tags column formula to all new rows as Tiller feeds in your new transactions. It’s pretty painless, but slightly annoying.
This workflow hasn’t been looked at by anyone else. We’ve used it for a little while now and haven’t seen any problems crop up. Still, I’d use caution in adding this solution!
I really like this, @brianvotoole. It’s a great concept and really well executed. The (optional) Tags column can be a bit challenging to keep updated— especially when there are multiple tags in the cell or when performing bulk updates— and this workflow is a huge help.
I was riffing on your formula a bit. I probably would have written it like this: =TEXTJOIN(",",TRUE,ARRAYFORMULA(IF(P2:S2,$P$1:$S$1,"")))
I trust ARRAYFORMULA() to be more performant than FILTER() but this is entirely based on intuition. If someone has data on this, I’m all ears.
Since we don’t have a solve on filling the formula all the way down, I’d leave out the bit that creates the header and just add that manually. That part of the formula is most useful when writing ARRAYFORMULA() headers that fill to the bottom.
I agree it would be cool if you could get it to populate all the way to the bottom (i.e. not need to add the formula to new rows). The challenge is that the ARRAYFORMULA() would need to work on two axes— across the tag columns and down to the bottom of the sheet and typically they don’t do this well. I’m intrigued by this problem and will continue to think on it. (Maybe @jono has an idea…?)
I goofed around with this some more, @brianvotoole. It’s not super elegant but one approach to creating an ARRAYFORMULA() in the header to populate the functionality down the column (into new rows) is to create discrete tests for each tag in the header formula (i.e. rather than using an ARRAYFORMULA() or FILTER() to iterate horizontally across the columns.
With four tag checkbox columns, my header formula looks like this: ={"Tags";ARRAYFORMULA(REGEXREPLACE(REGEXREPLACE(if(P2:P,$P$1,"")&","&if(Q2:Q,$Q$1,"")&","&if(R2:R,$R$1,"")&","&if(S2:S,$S$1,""),"^,*|,*$",),",+",","))}
You can see that it is performing a test on tag checkbox columns P, Q, R and S. If you had more columns, you’d just want to add more instances of the &","&if(S2:S,$S$1,"") test with the appropriate column letters.
Annoyingly, I learned that the JOIN() and TEXTJOIN() functions don’t work well within the ARRAYFORMULA(), so I used REGEXREPLACE() to remove the leading, trailing, and duplicate commas. The interior REGEXREPLACE() strips the leading and trailing commas. The enclosing REGEXREPLACE() reduces duplicate commas to a single instance. Perhaps someone smarter than me can figure out how to perform this feat more elegantly with a single call to REGEXREPLACE().
I independently came up with the same concept that you did @randy using ARRAYFORMULA(), an expanding number of IF() statements and a REGEXREPLACE() to get rid of the final comma.
My version is: ={"Tags";ARRAYFORMULA(REGEXREPLACE(IF(P2:P,$P$1&",","")&IF(Q2:Q,$Q$1&",","")&IF(R2:R,$R$1&",",""),",$",))}
If you need more columns with Tags options add more &IF(?2:?,$?$1&",","") blocks where the ? is replaced by the Column letter. Reduce those blocks if using less columns.
I always add the comma at the end of a tag segment and then just remove the last comma at the end.
Yes, arrayformula() is very powerful. One arrayformula() in just 1 cell can perform calculations in many rows and cells.
While arrayformula() works with many other functions inside its ( ), such as IF(), SUMIF() etc, there are some functions such as SUMIFS() that it doesn’t work with. There are ways around that though.
What I like best about ARRAYFORMULA() is that you don’t have to worry if a calculation is missing from a cell in the guts of your sheet (e.g. from an added row or a mistakenly deleted formula). You can also set & change the formula in one place rather than having to worry if the same formula is used in all cells in a column.
One thought would be to use a dropdown to select the tag you want . This can allow you to have up to 4 seperate tags per transaction, but have unlimited tag options in the dropdown. Just a thought on scaling this.
