Validação de dados no Excel - todos os valores em uma lista delimitada por vírgulas dentro do intervalo de células

Question

Eu tenho uma tabela de referência de memorandos e algumas informações sobre eles, incluindo quais outros memorandos eles referenciam e são referenciados. Quero fazer uma regra de validação de dados que apareça em vermelho se algum dos valores na lista de referências delimitada por vírgulas não estiver em minha lista de memorandos (em outras palavras, estamos perdendo informações ou o usuário pode ter feito uma erro de digitação). Qualquer número de outros memos pode ser referenciado. Os números da nota para cada entrada estão na coluna A, as referências estão na coluna G. Linha de cabeçalhos A, dados nas seguintes linhas, a serem adicionados regularmente.

A ............ G

LF100009 ....... 400 < - vermelho

HS100011 .......

RT100012 ....... LF100009

LA100015 ....... LF100009, RT100012

ME100020 ....... HS100011, RT1000012, LA100015 < - vermelho

KE100050 ....... LA100011, LA100015 < - vermelho

    

Answer 1

Não é nada elegante, mas isso funcionaria para até 3 referências. Você pode combinar com a formatação condicional conforme necessário ou colocar em uma coluna auxiliar.

=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

E aqui está um UDF que coloca um valor VERDADEIRO / FALSO em uma célula auxiliar. A fórmula seria parecida com =testReferences(G2,A:A,", ")

O seguinte código é colado no editor do VBA. Funciona para referências ilimitadas.

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