Проверка данных в Excel - все значения в списке через запятую в пределах диапазона ячеек

Question

У меня есть справочная таблица заметок и некоторая информация о них, в том числе какие другие заметки они ссылаются и на которые ссылаются. Я хочу создать правило проверки данных, которое будет отображаться красным цветом, если любое из значений в списке ссылок, разделенных запятыми, отсутствует в моем списке заметок (другими словами, нам не хватает информации, или пользователь, возможно, сделал опечатка). На любое количество других заметок можно ссылаться. Номера памяток для каждой записи указаны в столбце A, ссылки - в столбце G. Строка заголовков A, данные в следующих строках, которые необходимо регулярно добавлять.

............г

LF100009 ....... 400 <- красный

HS100011 .......

RT100012 .......LF100009

LA100015 .......LF100009, RT100012

ME100020 .......HS100011, RT1000012, LA100015 <- красный

KE100050 .......LA100011, LA100015 <- красный

Answer 1

Это совсем не элегантно, но это будет работать до 3 ссылок. Вы можете комбинировать с условным форматированием по мере необходимости или помещать в вспомогательный столбец.

=IF(IF(IF(G2="",TRUE,LEN(G2)-LEN(SUBSTITUTE(G2,",",""))+1)=TRUE,1,
IF(IF(G2="",TRUE,LEN(G2)-LEN(SUBSTITUTE(G2,",",""))+1)=1,COUNTIF(A:A,G2),
IF(IF(G2="",TRUE,LEN(G2)-LEN(SUBSTITUTE(G2,",",""))+1)=2,SUMPRODUCT((COUNTIF(A:A,LEFT(G2,FIND(",",G2)-1)))*(COUNTIF(A:A,MID(G2,FIND(",",G2)+2,LEN(G2))))),
IF(IF(G2="",TRUE,LEN(G2)-LEN(SUBSTITUTE(G2,",",""))+1)=3,SUMPRODUCT((COUNTIF(A:A,LEFT(G2,FIND(",",G2)-1)))*(COUNTIF(A:A,MID(G2,FIND(",",G2)+2,LEN(G2)-FIND("@",SUBSTITUTE(G2,",","@",IF(G2="",TRUE,LEN(G2)-LEN(SUBSTITUTE(G2,",",""))+1)-1))-1)))*(COUNTIF(A:A,MID(G2,FIND("@",SUBSTITUTE(G2,",","@",IF(G2="",TRUE,LEN(G2)-LEN(SUBSTITUTE(G2,",",""))+1)-1))+2,LEN(G2))))),
FALSE
))))=1,TRUE)

Answer 2

А вот UDF, который помещает значение TRUE / FALSE в вспомогательную ячейку. Формула будет выглядеть как =testReferences(G2,A:A,", ")

Следующий код вставляется в редактор VBA. Это работает для неограниченных ссылок.

Function testReferences(text As Range, list As Range, delimiter As String)
    Dim arr() As String
    Dim found As Boolean

    If text = "" Then
        found = True
    Else
        arr = Split(text, delimiter)            
        For i = LBound(arr) To UBound(arr)
            If Application.WorksheetFunction.CountIf(list, arr(i)) = 1 Then
                found = True
            Else
                found = False
                Exit For
            End If
        Next
    End If

    testReferences = found
End Function