Data Validation on Table Column with Multiple "Tags" Per Cell, Checked against List

[mc-lemons]

Hello!

Below is a simplified example, but I'm looking to have a table where one of the columns is for entering "Tags". I would like to be able to enter any number of valid tags from the "Master Tag List" and separate each value by a comma and space as shown below. I figured this could potentially be done with a formula via data validation, but need some help with the formula. So for example, if "Tag 4" or "Tag 5" as shown below in A5 & A6 were entered (or any typo was made), an error message would pop up because those aren't in the Master Tag List.

I am using Microsoft 365, so can use all the nice new fancy formulas and dynamic arrays.

Thanks for your time and consideration!

 

[kevin9999]

If you're open to a VBA solution, why not use a worksheet change event module, coupled with data validation in column A? The following code would go in the worksheet module of the sheet in question (right click the sheet tab name, select View Code and put the code in the blank window that appears). Make as many cells in column A tied via data validation to your existing cells in column D (because it's a table it will expand as you add tag names) as you think you'll ever need. That way, only Tag names listed in column D could ever be added to column A.

The code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Oldvalue As String, Newvalue As String
    Application.EnableEvents = False
    On Error GoTo Exitsub
    If Target.Column = 1 Then
        If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
            GoTo Exitsub
            Else
            If Target.Value = "" Then GoTo Exitsub Else
            Newvalue = Target.Value
            Application.Undo
            Oldvalue = Target.Value
            If Oldvalue = "" Then
                Target.Value = Newvalue
                Else
                If InStr(1, Oldvalue, Newvalue) = 0 Then
                    Target.Value = Oldvalue & ", " & Newvalue
                Else
                Target.Value = Oldvalue
                End If
            End If
        End If
    End If
Exitsub:
    Application.EnableEvents = True
End Sub

Link to demo file: Data validation multiple selections.xlsm

 

[mc-lemons]

Thanks for taking the time to write the code! However, it doesn't appear to be working how I want. It works if a single tag is entered (A5), but not if I try entering multiple tags separated by a comma (A6), even if they are both valid tags (see below)

Also, I prefer non-VBA most of the time in Excel, largely due to it clearing the undo stack.

 

[Cubist]

What you're asking for is not possible with an Excel formula. Excel looks at the content of the entire cell as 1 string.

 

[mc-lemons]

I did end up finding a solution. I'm sure there's a more efficient way to do it, but below is the formula I came up with. I also had to use to helper columns since table references can't be used in Data Validation. Below is a screenshot and the spreadsheet can be found here.

Formula:
=IF(COUNTIF($F$2#,"<>0")=0,TRUE,IF(SUM(IF(ISNUMBER(XMATCH(TEXTSPLIT(TEXTJOIN(",",,SUBSTITUTE(FILTER($F$2#,$F$2#<>0),", ",",")),,","),$I$2#)),0,1))>0,FALSE,TRUE))

 

[Cubist]

What happens if you enter Tag 2, Tag 3?

 

[mc-lemons]

It works as expected, aka no validation error. I linked the spreadsheet, so let me know if you have a different experience or get it to break. So far it's working with every test I've tried. I guess the one thing it allows that I'd prefer it didn't, is that duplicates of valid tags are permitted. So you can enter "Tag1, Tag1" without validation error, but otherwise happy with the result.

 

[Cubist]

In your attached file...

 

[mc-lemons]

I didn't test it in Google sheets. Download it in xlsx format and try it in Microsoft Excel