šŸ† 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.

We also awarded @jpfieber $200 for refining the template to share his template into the add-on.

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

Hey @jpfieber,

First of all, thanks for creating this sheet! Very useful to have a single source of truth for all tags.

Iā€™m currently having an issue with data validation on my transaction sheet. It seems like from the discussion above that the data validation rules should have automatically been added once the sheet was installed? However this did not happen to me ā€“ it might be because I already had a ā€œTagsā€ column.

How does one add validation to the tags column that references the ā€œmasterā€ tags list from Tags Manager?

Also, would the data validation rule support multiple tags for a transaction, i.e. validating that both ā€œTravelā€ and ā€œCalifornia2023ā€ in ā€œTravel,California2023ā€ are valid tags? I have a feeling Sheets doesnā€™t support this today.

Also, would the data validation rule support multiple tags for a transaction, i.e. validating that both ā€œTravelā€ and ā€œCalifornia2023ā€ in ā€œTravel,California2023ā€ are valid tags? I have a feeling Sheets doesnā€™t support this today.

Actually, this might be possible since you can use REGEXMATCH as a formula for data validation. If the Tag Manager constructs a regex for all the comma-separated tags in a hidden column, one use that for validating multiple tags. The downside to this approach is that youā€™ll lose the dropdown capability and would have to keep the field a string.

Edit:
To close the loop hereā€¦

  1. Hereā€™s a regular expression you can drop into the Tag Manager sheet:
    =ā€œ^((ā€ & REGEXREPLACE(JOIN(ā€œ|ā€,UNIQUE($C$2:$C)), ā€œ|$ā€, ā€œā€) & ā€œ),?)+$ā€
    (I named this range as ā€œTagsRegexā€)

This results in a regex that looks like this:
^((TagA|TagB|TagC),?)+$
which will only match if the list of tags follows the pattern.

  1. Then for data validation for your ā€˜Tagsā€™ column in your Transactions sheet, you can add this custom formula over the range:
    =REGEXMATCH(P2, TagsRegex)

One issue with this is that any ā€œinvalidā€ or new tag you type will automatically update the regex and wonā€™t invalidate the input, so if thereā€™s a static list of tags you want to validate against, change #1 to reference that range instead.

The documentation on the support page runs through the steps:
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!

1 Like

You canā€™t use the dropdown menu to choose multiple tags, but you can add one tag, then manually add a comma and type in the second tag, and Tag Manager will recognize them as separate tags.

Thanks, I totally missed that!