Lista suspensa do Excel com vários critérios

Question

Digamos que tenhamos uma lista de pessoas que participam de uma entrevista. Essas pessoas têm basicamente dois papéis: entrevistador e entrevistado . O que eu quero fazer é criar um menu suspenso que permita apenas os participantes que têm o papel entrevistador .

Como em:

Column A | Column B
_________|_____________    
person 1 | interviewer
person 2 | interviewed
person 3 | interviewed
person 4 | interviewer

E quando eu criar uma lista suspensa do excel, ela só mostrará as pessoas 1 e 4 como possíveis valores a serem inseridos.

Eu tenho uma inclinação que isso usaria uma combinação de IF , mas eu estou no escuro sobre como implementar isso.

    

Answer 1

Você pode fazer isso classificando a coluna B por A-Z e abrindo o Name Manager pressionando CTRL + F3 . Isso pode ser encontrado na guia da faixa de opções Fórmulas e no Gerenciador de nomes na área Nomes definidos.

Clique em New... , nomeie esta lista futura como Interviewed e no tipo de campo Refers to: nesta fórmula.

=OFFSET(INDEX(Sheet1!$A1:$A10,MATCH("interviewed",Sheet1!$B1:$B10,0)),,,COUNTIF(Sheet1!$B1:$B10,"interviewed"))

Você pode repetir essas etapas para uma segunda lista de entrevistadores, mas use essa fórmula. =OFFSET(INDEX(Sheet1!$A1:$A10,MATCH("interviewer",Sheet1!$B1:$B10,0)),,,COUNTIF(Sheet1!$B1:$B10,"interviewer"))

Os nomes precisam da referência de folha absoluta.Em seguida, crie sua Lista de Validação de Dados e Permitir uma Lista e no campo Origem você pode pressionar F3 para exibir seus nomes e selecioná-los para que sejam colados você.

Não, você deve ter uma caixa suspensa com as pessoas que têm entrevistador ao lado delas.

A OFFSET() funtion está usando a função INDEX() para examinar A1: A10 e retornar uma referência de célula para nós. MATCH() está sendo usado para fornecer a linha que contém a primeira ocorrência de "entrevistador". Então Index retorna o primeiro parâmetro de Offset. as três vírgulas seguidas são porque não queremos mover o ponto de referência por nenhuma linha ou coluna.

No entanto, queremos mudar a altura! Essa é a mágica porque o Offset pode ser usado para criar um intervalo para nós. Nossa altura é calculada por COUNTIF() , que examina a coluna B1:B10 e retorna dois no seu exemplo. Agora, as funções de deslocamento usam a primeira célula de referência e uma altura de dois para criar um intervalo para nós que contenha os nomes dos entrevistadores.

Novamente, isso só funcionará se você puder classificar por coluna B, de modo que todos os valores entrevistados e entrevistadores fiquem em um único intervalo.