Como combinar um intervalo e um valor determinado em uma lista de validação?

Question

Eu tenho uma célula com uma lista suspensa (usando "Validação de dados").

A lista vem de um intervalo nomeado em outra planilha.

Eu quero que a lista suspensa mostre o intervalo nomeado e outro valor predefinido. Por exemplo, se o intervalo for assim:

1
2
3
4

Eu quero que o menu suspenso apareça:

1
2
3
4
17 (my predefined value)

Como conseguir isso?

    

Answer 1

Se você quiser usar os dados de validação para criar uma lista, é impossível, você receberá a mensagem de erro: Você não pode usar operadores de referência (como uniões, interseções e intervalos) ou constantes de matriz para critérios de formatação condicional.

Por isso, sugiro que você tente usar o código como uma solução alternativa para ajudar a resolver esse problema.

Sub SetDataValidation() '

Dim WS As Worksheet

Set WS = Worksheets("Sheet1")

Dim rng As Range

'get range of named range

Set rng = WS.Range("NUMBER")

Dim ARR As Variant

ReDim ARR(1 To 1) As Variant

'add cell value from named range to an array

For i = 1 To rng.Cells.Count

ARR(UBound(ARR)) = rng.Cells(i).Value

ReDim Preserve ARR(1 To UBound(ARR) + 1) As Variant

Next i

'add your predefined value to the array

ARR(UBound(ARR)) = 17

'set validation for B1 with the combined array

With WS.Range("B1").Validation

.Delete

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(ARR, ",")

End With

End Sub

Na minha amostra, eu nomeei o intervalo como NUMBER e obtive o resultado:

    

Answer 2

Eu tive um problema semelhante ao seu. Eu tinha um intervalo nomeado Dyn_ParamList_KillDate com uma lista de datas 1/1/2018, 1/2/2018, etc... . Esse intervalo nomeado dinâmico é preenchido por um processo e muda de tamanho. O problema é que, às vezes, a lista estaria vazia e vazia para o dropdown estava causando problemas.

Então, para minha solução, criei outro intervalo nomeado Range_Writeback_ModelParamDate que sempre tem um valor. Em seguida, alterei a lista de validação de dados de =Dyn_ParamList_KillDate TO =IF(Dyn_ParamList_KillDate<>"", Dyn_ParamList_KillDate, Range_Writeback_ModelParamDate) .

Uma vez que adicionei esse segundo intervalo, garanti que ele sempre tivesse um valor e mudei a fórmula suspensa para usar IF e puxar de um dos intervalos-- comecei a obter corretamente os resultados com base em dois intervalos. Espero que isso ajude alguém.

A ideia para esta solução veio daqui: link