🏆 Tag Manager - Google Sheets

Google Sheets Show & Tell
,
21

@jpfieber SOLVED WITH ABSOLUTE REFERENCES. I DON’T KNOW WHY… Thx. I’m stumped. I’ve followed all the instructions and my tag dropdowns on my transaction page include all the tags in the tag manager for around the first 10 rows… then some are missing till there are none in subsequent rows. AND any tags which I already had in my Tags column are flagged as not valid. Any ideas???

22

@isussman1228 I had the same thing. Changing the Data Validation criteria range to =‘Tag Manager’!$C$2:$C did the trick.

2 Likes
23

For others wanting to add multiple tags, you may need to swich to “Show a warning” from “Reject the input” which was selected by default in the data validation rule.

Also, to switch the dropdown to look like others in Tiller, switch the “Display Style” to “Arrow” from “Chip” (which was default).

image

1 Like
24

Not sure if anyone else is experiencing this, but the Transactions column in the Tag Manager no longer pulls Tags from the Transactions Sheet. The other columns work, but that one doesn’t. It’s completely empty even though there are dozens of tags. I even imported a fresh copy of the Tag Manager from the Community Add Ons, same issue right out of the box.

25

It looks like this may have to do with number of rows in the Transactions sheet. Is there a limit as to how many rows the formula in cell J1 on the Tags Manager can perform? I’ve been doing some testing on the content and it seems like that’s the case for me. Around 17,200 rows or so in my case.

To be exact, once I get past 17205 rows (not counting the header of the Transactions sheet, 17206 including that), the formula outputs no data in the Transactions tag column (J). Alternatively, if I change the starting cell in F2 on the Tag Manager from F2 to F158, the tags are all correctly displayed in column J. However, if I change that value to F157, they all disappear. I’ve checked row 157 for corrupted data, it’s clean. I even copied and pasted row 158 data into row 157 to rule out data issues. If I subtract 157 from the total number of rows in my sheet (156 not including the header row), I get 17362 - 156 = 17206. Both instances, when there are more than 17206 rows in my Transactions sheet, column J no longer outputs any tags.

If I copy the Formula from K1 to J1, and then change any instance of 3 in J1 to to 2, it outputs the data in Column J, albeit all unique instances of Tags, including comma delimited tags (i.e. more than 1 tag is output as a single tag). It used to split these apart and just list the unique tags, not the entire comma delimited tag. So, the difference between J1 and K1 is that the J1 formula has Transpose, Split, and Join functions. I wasn’t able to find anything about row limitations for these functions, so I’m not sure at this point.

@Mark.S , do you use this sheet at all? Any ideas?

26

I’ve got about 7000 transactions so I haven’t come across any issues. You can see from the G1 cell, that by default it doesn’t have a defined range, it should try to grab as many rows as there are. It’s possible that it’s as you suggest, perhaps one of the functions has an undefined limit.

27

Thanks. My workaround here is the following in case anyone else has a lot of rows and has this issue:

  1. Create 6 additional helper columns on the right. P->U for me.
  2. In Q1, enter the Formula from K1:
={$E3;IFERROR(SORT(UNIQUE(FILTER(INDIRECT($G3),INDIRECT($G3)<>""))),IFERROR(1/0))}
  1. Change all instances of 3 to 2 (i.e. E3 → E2, G3 → G2) in cell Q1.
  2. This will output all uniques instances of tags in the Tags column in the Transactions sheet, including instances of multiple tags that are not split apart.
  3. Next, copy E2 to G2. Paste those values in S2 to U2.
  4. In S2, change Transactions to Tag Manager, in T2 change F2 to Q2.
  5. In J1, replace the formula with the following:
={$S2;iferror(SORT(UNIQUE(TRANSPOSE(SPLIT(JOIN(",",FILTER(INDIRECT(U2),INDIRECT(U2)<>"")),",")))))}
  1. Hide Helper Columns P->U
  2. You are now able to see all of the unique single Tags in column J, including instances of more than 1 tag broken apart into single tags, as desired.

There may be a way to make this all work in a single formula without the helper data, but I wasn’t able to figure that out at the moment.

1 Like
28

I don’t use this sheet, but JOIN has a 50,000 character limit. The Tags Report would have the same issue I believe.

Looks like your work-around probably mitigates it.

1 Like
29

In the process of finding a solution for this, I found a workaround for the Data validation errors for more than 1 tag:

1 column in the Tag Manager can list your unique Transactions tags (Column J), and another one can list your Transaction Tags (which includes more than one tag per transaction) (Column K, for example). Then, you get an output in column C of the Single Unique Tags (from Column J) but also the groupings of multiple tags that are found in your Transactions (Column K). Doing it this way, I can now choose from the dropdown on a transaction and select multiple pre-existing instances of Tags with 1 click.

In terms of validation, I have it set to reject any Tag not listed in Column C. If I want to add a new Tag (or multiple tags for a new transaction), I just go into Column I on the Tag Manager, enter a new combination or single tag, then select that tag on the current transaction Tag dropdown cell on the Transactions sheet. Once the tag is selected, I remove it from the Manual column on the Tag Manager. Doing it this way, I won’t have any issue accidentally misspelling tags.

No more red ticks when using multiple tags, clean data, seems to work.

Let me know if doing it this way would break anything else. Haven’t looked into that much yet.

30

BTW, you might be able to remove the iferror to confirm the reason for the original issue.

1 Like
32

Appreciate all the posts and knowledge shared in this thread. I use tags pretty extensively, including multiple tags per transaction. I was greatly missing data validation/pulldown list capability for tags.

I have implemented essentially what was proposed by @mu3484343 and I think it’s a great solution. I create the column with the groupings of multiple tags in a slightly different fashion. I created a column which uses chip style dropdown lists (which allows for multiple selections per cell). In this column I manually create the tag combinations which are needed. The source for the dropdown list is the column containing the list of single tags (which are manually entered). This will prevent me from making a typo when creating tag groupings. When you create a cell with multiple tag selections, the text in the cell will be formatted with spaces after each comma as: “Tag1, Tag2, Tag3, …”. So I created another column using the substitute formula to get rid of the space after the commas (e.g. SUBSTITUTE(F2,“, “,”,”)). This implementation allows for a single source of truth for the tags and I don’t need to delete any entries from the manually entered columns.

So this is what my sheet looks like:

Col C: List of all single tags and tag combinations (flattened from Col E and Col G), use this for data validation for the Tags column in the Transactions sheet.

Col E: Manually entered list of single tags - only source of tag truth

Col F: Manually created combinations of tags - use Col E for data validation

Col G: Get rid of the spaces after commas from Col F

sheet
sheet2414×814 65.9 KB

1 Like
33

Hi, there. This thread has gotten my interest again. Can you please help me unpack this paragraph?

I followed your instructions above, so that I now have all single tags in Column J and all tags, including multiple tags strung together, in Column Q. How do I generate what you call here Column K (i.e., the groupings of multiple tags that are found in my Transactions)? And then how do I get the combination of Columns J and K to appear in Column C? Sorry, I know a lot about some things, but not about Google Sheets coding. Thanks for any advice you can give.

34

In the Tag Manager sheet, column C is where the tags are derived for the dropdowns in the Tags column in your Transactions sheet. Column C is a merge of all the tags found in columns I-O. Column I is where you can manually type in any tags you want, column J is an automated gathering of all the tags from the current Transactions sheet, and the rest are automated gatherings of tags from other sheets, which you can define in columns E-F. Unless you expanded the Tag Manager, there is no column Q there. Perhaps you’re referring to column Q in your Transactions sheet? If that’s the case, that’s where you’d apply the Data Validation to make the dropdown menus appear.

1 Like
35

Thanks. Column Q comes from @mu3484343’s modification above. . .

1 Like