Como posso usar células não adjacentes em outra planilha para um menu suspenso Validação de dados e mostrar apenas valores não em branco?

Question

Eu tenho algumas células não adjacentes em uma planilha que desejo usar como uma Source for Data Validation em uma célula em outra planilha. No entanto, parece que a validação de dados não permite isso de forma nativa.

Então, eu tentei fazer uma solução que envolvia colocar fórmulas em algumas células adjacentes em outra planilha que referenciaria os valores que eu queria da primeira planilha. Em seguida, nomeei o segundo intervalo e referenciei esse nome no campo Fonte de validação de dados.

Agora tenho um problema em que, se uma das células da primeira folha estiver em branco, uma célula na segunda folha que faz referência a uma em branco na primeira mostrará 0 em vez de ficar em branco. Eu tentei contornar isso fazendo algo como =IF(Sheet1!A1="","",Sheet1!A1) . No entanto, isso não realmente faz com que a fórmula resulte o mesmo que uma célula em branco.

Isso tudo se torna problemático quando quero que minha validação de dados inclua uma lista suspensa na célula. Minhas escolhas aqui parecem ser 0 ou uma linha em branco na lista suspensa sempre que houver espaços em branco nos dados de origem. Se os espaços em branco fossem verdadeiros espaços em branco, isso não aconteceria.

Então, existe alguma maneira de contornar isso?

Exemplo:

Folha1

A1 = Value1
A5 = Value2
A9 = Value3
A13 = Value4
A17 = (a célula está em branco)
A21 = (a célula está em branco)

Folha2

A1 =Sheet1!A1 (retorna Value1 )
A2 =Sheet1!A5 (retorna Value2 )
A3 =Sheet1!A9 (retorna Value3 )
A4 =Sheet1!A13 (retorna Value4 )
A5 =Sheet1!A17 (retorna 0 )
A6 =IF(Sheet1!A21="","",Sheet1!A21) (aparece em branco)

Sheet2!A1:A6 é denominado Validation . Em Sheet3!A1 , a Validação de dados é aplicada com a origem =Validation e uma lista suspensa na célula. O menu suspenso na célula mostra:

Value1
Value2
Value3
Value4
0

(célula em branco incluída)

Nestas condições, estou procurando uma configuração que resulte em uma lista suspensa em Sheet3!A1 , que mostre apenas Sheet2!A1:A4 , mantendo também Sheet2!A5:A6 disponível caso eles sejam preenchidos. Como alternativa, a lista suspensa em Sheet3!A1 deve mostrar apenas Sheet1!A1,Sheet1!A5,Sheet1!A9,Sheet1!A13 , mantendo também Sheet1!A17,Sheet1!A21 disponível caso eles sejam preenchidos.

Parece que preciso de uma maneira:

• Endereça diretamente as células não adjacentes em Sheet1 , em minha fonte de validação de dados
  OU
• Pegue as células no intervalo Validation em Sheet2 para realmente retornar células em branco quando seus destinos em Sheet1 estiverem em branco.

Answer 1

Usando o método descrito em sua postagem original Como eu faço um drop-down de validação de dados excluir espaços em branco .

Junte isso com esses métodos:

Public Sub ClearDataValidation(destrng As Range)
   destrng.Validation.Delete
End Sub

Public Sub LoadDataValidation(srcrng As Range, destrng As Range)
   'Verify a 1x1 sized Range was passed
   If destrng.Rows.Count <> 1 Or destrng.Columns.Count <> 1 Then
      InvalidValue destrng.Worksheet, "LoadDataValidation", _
            "Range: " & destrng.name & " was passed to method. This method expects a " & vbCrLf & vbCrLf & _
            " 1 Row x 1 Column Range to be passed.  Anything outside of the 1x1 " & vbCrLf & vbCrLf & _
            "size will result in invalid conditions"
      Exit Sub
   End If

   With Range(destrng.Address).Validation
      .Delete
      .Add xlValidateList, xlValidAlertStop, xlBetween, DistinctValues(srcrng)
   End With
End Sub

Uso:

LoadDataValidation Range("Table1[column1]"), Range("destinationCell")