Excel 2013 - Validação de dados - Crie uma lista suspensa com valores se um segundo critério for correspondido

Question

Vou simplificar o meu problema tanto quanto possível. Eu tenho duas folhas no Excel. Na primeira folha estou criando uma lista de locais. Eu preciso escolher uma área onde o local está situado na coluna A antes que eu possa digitar o local na coluna B. Na próxima coluna (C), haverá um ID que é criado automaticamente (para o primeiro local ID-001, o segundo recebe o ID 002, etc.).

Na minha segunda planilha estou criando uma lista de ativos. Portanto, novamente, escolho uma área na coluna A e digito o nome do ativo na coluna B. Agora eu quero ter uma lista suspensa na coluna C, onde me mostra apenas os IDs da primeira tabela, onde a área é a mesma. Eu tentei usar a função OFFSET, mas o ponto que está faltando é algum tipo de "select if" (como countif ou sumif) para limitar a lista de IDs para aqueles que possuem a mesma área.

Pode haver muitos locais e recursos em cada área, mas todos os locais e todos os recursos só podem ser mapeados para um local.

Aguardamos suas respostas! Uma solução de excel ou uma solução de vba seria incrível.

    

Answer 1

Sugiro criar uma coluna auxiliar que inclua valores exclusivos para usar no VLOOKUP. Por exemplo; Digamos que você tenha Área1, Área2, Área3 e Área1 (novamente) e continue. Mesclar esses valores com valores incrementais (por exemplo, 01, 02, 03, ...) e criar 01Area1, 01Area2, 01Area3 e 02Area1 . Agora você sabe o que chamar para cada item da lista na segunda folha. Se você selecionar Área1 na coluna A, precisará pesquisar número incremental & Area1 . Números incrementais por valores podem ser criados pela função COUNTIF que usa o intervalo de expansão:

=COUNTIF($A$2:A2,A2)

Ao expandir o intervalo, a fórmula conta apenas os valores até sua própria linha. Único handicap deste método você precisa criar itens de lista em em outro lugar

Aqui estão dois links que descrevem o uso semelhante.:

1. link
2. link

Answer 2

Supondo que seja aceitável ter a tabela Locais classificada, a solução mais simples envolve apenas uma fórmula de validação de dados relativamente curta e alguns intervalos nomeados. Não são necessárias colunas ou linhas auxiliares.

Configure duas planilhas de exemplo, Locations e Assets , assim:

Adicionedoisnomesdefinidos:

1. Areas→=Locations!$A:$A
2. IDs→=Locations!$C:$C

Porfim,adicioneumavalidaçãodedadosdalistasuspensanacélulaaC2naplanilhaAssetscomaseguintefórmulaepreencha/copieecoleacélulaparabaixo:

=INDEX(IDs,MATCH(A2,Areas,0)):INDEX(IDs,MATCH(A2,Areas,0)+COUNTIF(Areas,A2)-1)

As desvantagens desse método são:

• Os nomes definidos precisam ser usados
• A tabela Locais deve ser classificada
• Outras tabelas na planilha Locations não podem cruzar com segurança a coluna Area , a menos que seja garantido que elas não contenham valores correspondentes aos valores Area da tabela de local.
  
  

A seguinte solução alternativa supera as desvantagens, exceto a de outras tabelas. No entanto, ele usa células na planilha de Ativos para armazenar os itens das listas suspensas.

A planilha de Ativos é configurada com colunas adicionais:

Destavez,afórmuladevalidaçãodedadosC2é:

=IF(SUMPRODUCT(--ISNA(C2:INDEX(2:2,COLUMN(C2)-1+COUNTA(C2:INDEX(2:2,COLUMNS(2:2))))))=0,$I$1,C2:INDEX(2:2,COLUMN(C2)-1+COUNTA(C2:INDEX(2:2,COLUMNS(2:2)))-SUMPRODUCT(--ISNA(C2:INDEX(2:2,COLUMN(C2)-1+COUNTA(C2:INDEX(2:2,COLUMNS(2:2))))))))

Digite esta fórmula em D2 e preencha:

=
IF(
  SUMPRODUCT(
    --ISNA(
      E2:INDEX(
        (2:2),
        COLUMN(E2)-1+MAX(1,COUNTA(E2:INDEX(2:2,COLUMNS(2:2))))
      )
    )
  )=0,
  "ERROR",
  "ok"
)

Matriz multicelular - insira essa última fórmula nas células, começando em E2 e indo o mais longe possível para o número máximo de locais esperados em uma área (para o exemplo mostrado, usei E2:I2 ):

=
INDEX(
  Locations!$C:$C,
  IFERROR(
    SMALL(
      IFERROR(1/(1/(
        (Locations!$A$2:INDEX(Locations!$A:$A,COUNTA(Locations!$A:$A))=IF($A2="",NA(),$A2))
        *ROW($A$2:INDEX($A:$A,COUNTA(Locations!$A:$A)))
      )),FALSE ),
      COLUMN(INDEX(2:2,1):INDEX(2:2,MAX(2,COUNTIF(Locations!$A$2:INDEX(Locations!$A:$A,COUNTA(Locations!$A:$A)),$A2))))
    ),
    NA()
  )
)