Tag Manager - Google Sheets

What is the goal of your workflow? What problem does it solve, or how does it help you?
As I’ve started to use Tags increasingly, I found that managing them has become an issue. Based on work that @Brad.warren did with the Category Schedule template, I created a Tags sheet, and used data validation on the Transactions sheet to grab a list of tags from it. This was a good start, but I was already using tags for other workflows that didn’t fit well with this solution, and also found that when I wanted to use a new tag, I’d often forget to add it to the Tags sheet.

After a few iterations, I decided the best option was to build a system that would import tags from any chosen sheets (it does ‘Transactions’ out of the box), and includes a ‘manual’ list of tags as well (great option for when you first start using tags). I borrowed a formula from the Tags Report template that pulls in the existing ‘Transactions’ tags and handles multiple comma separated tags.

TagManager

Other sources can be added by changing the placeholder info (name of sheet, and cell of the first tag of a vertical list). For example, if you use tags on your Categories sheet, you might add that, or if you use the Category Schedule template you can includes those tags:
tagManager1

Please describe your workflow. What are the sheets? Does it use any custom scripts or formulas?
Install the Tag Manager from the Tiller Community Solutions Add-on. Make changes in the green cells, either manually entering any tags you want to appear in the drop-down for the Tags column, or entering info on other sheets you have with lists of tags.

Then, on your ‘Transactions’ sheet, create a ‘Tags’ column if you don’t already have one (doesn’t matter where as long as it is called ‘Tags’). Select the first cell below the ‘Tags’ label, scroll to the bottom of your sheet, hold down the shift key, and select the last cell in that column. This should have selected all the cells in your tags column except for the cell the label is in. Go to the ‘Data’ menu and choose ‘Data Validation’. Make sure ‘Criteria’ is set to “List from a range” (it should be by default) and in the box to the right of that, where it says “Enter a range or formula”, enter ='Tag Manager'!C2:C. This tells it to look on the Tag Manager sheet in the cells from C2 through the bottom of column C. Click ‘Save’ and you should see drop down menu indicators appear on all the cells in your Tags column. Click on one and you should see a list of any tags you already had, along with any tags you manually entered or pulled from other sheets!

Anything else you’d like people to know?
If you only use one tag per transaction this works very smoothly. If you use more than one tag on a transaction (separated by a comma with no spaces), then they will need to be manually added (can’t use the dropdown for anything more than one tag) and the cell will have the red ‘Invalid’ triangle since the multiple tags don’t exist in the list on the Tags sheet.

Is it ok for others to copy, use, and modify your workflow?
Yes, please, and let me know if you have problems or ideas for improvements!

If you said yes above, please make a copy of your workflow and share the copy’s URL:
Note: I rebranded this from “TagComb” to “Tag Manager” but can’t get the name on the sharing link to change…

I released an Excel version of this here, and rebranded this one to match with the new name “Tag Manager”.

@jpfieber Can you elaborate on the purpose of the Tag Manager?

Thank you.

ScottC

Sure. In the same way that the Transaction sheet includes drop down menus to choose Categories, I wanted to have drop down menus for Tags (I added a ‘Tags’ column to my Transactions sheet so I could use templates like “Tags Report”). I realized I have a few different sheets that used Tags, and I wanted them to have drop downs as well. The best way to make that possible was to gather all the tags from those sheets into one location, and then use that as the source for any tag drop down menus. I then also added the option for pre-populating any tags that might not already be in use.

1 Like

I am using the Holiday Planner and attempting to add the sheet to the tags manager. The issue I am having is that the formula seems to only account for an entire column and not a range within that column. The way that the Holiday Planner is set up, and I may encounter other solutions with this same type of setup, has a range of tags starting on A1 and ending on A32. How do I set it to only pick up the tags within a set range as opposed to the entire column which in this specific example inputs items that are not tags but happen to be located below A32?

I am I have set up certain tags within my Autocat sheet that have multiple tags separated by commas and no spaces. When I add that as an option (Example: Monthly,Recurring & Annual,Recurring) I am able to select both of them under the dropdown as a single unit tag if that makes sense. It’s a good way to incorporate multiple tags on the fly if you already know a transaction will always have those same tags associated with it. I am sure if this fluctuates you could just manually input those.
This is a great addition to my foundations and really love all your work.

Glad it’s mostly working for you. To do what you want, you could modify the formula in column F that calculates the range. For example, here’s the default formula for Placeholder 5:
=if(D10<>"","'"&D10&"'!$"&REGEXREPLACE(E10,"[^[:alpha:]]", "")&"$"&VALUE(REGEXREPLACE(E10,"[^[:digit:]]", ""))&":$"&REGEXREPLACE(E10,"[^[:alpha:]]", ""),"")
If you change it to the following, it will stop at row 32:
=if(D10<>"","'"&D10&"'!$"&REGEXREPLACE(E10,"[^[:alpha:]]", "")&"$"&VALUE(REGEXREPLACE(E10,"[^[:digit:]]", ""))&":$"&REGEXREPLACE(E10,"[^[:alpha:]]", "")&"32","")
You can see it’s just a small addition to the formula at the end that appends a “32” to the end of the already calculated range.

Thank you for the quick reply. These formulas are like another language to a noob.

Yea, it’s confusing to me as well, REGEX is a whole word of it’s own. Thankfully I found good examples to help me get a working formula.

At Joseph’s request, I published this template into the Tiller Community Solutions Add-on. Now it is easier than ever to discover, try, and maintain this solution.

Just found this one. Very helpful since I plan to start using Tags more. Thanks.

1 Like

Pulled my transactions in this morning and the Tag column in Transactions is not automatically populating the Tag dropdown in any new transaction for some reason. Range is ‘Transactions’!$F$2:$F on Tag Manager so not sure why that would be happening…

Try taking the dollar signs out. I just looked at mine, and it doesn’t have the dollar signs, and automatically adjusts the end of the range to the last row number.

1 Like

Backed those out. Had to rebuild the validation rule. Will check tomorrow when I download transactions again.

Confirmed backing out the dollar signs fixed this. Thanks!

Great, I’ll update the documentation to reflect this, thanks for letting me know!

1 Like