Vou simplificar meu problema o máximo possível. Eu tenho duas planilhas no Excel. Na primeira planilha estou criando uma lista de locais. Preciso escolher uma área onde o local está situado na coluna A antes de poder digitar o local na coluna B. Na próxima coluna (C) haverá um ID que será 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 quero ter uma lista suspensa na coluna C onde me mostre apenas os IDs da primeira tabela onde a área está o mesmo. Tentei usar a função OFFSET, mas o ponto que falta é algum tipo de "selecionar se" (como countif ou sumif) para limitar a lista de IDs àqueles que possuem a mesma área.
Pode haver muitos locais e ativos em cada área, mas cada local e cada ativo só podem ser mapeados para um local.
Aguardo suas respostas, pessoal! Uma solução Excel ou uma solução VBA seria incrível.
Responder1
Sugiro criar uma coluna auxiliar que inclua valores exclusivos para usar em VLOOKUP. Por exemplo; digamos que você tenha Area1, Area2, Area3 e Area1 (novamente) e continua. Mescle esses valores com valores incrementais (por exemplo, 01, 02, 03, ...) e crie 01Area1, 01Area2, 01Area3 e02Área1. Agora você sabe como chamar cada item da lista na segunda planilha. Se você selecionarÁrea1na coluna A, então você precisa pesquisarnúmero incremental&Área1. Números incrementais por valores podem ser criados porCONT.SEfunção que usa 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. A única desvantagem deste método é que você precisa criar itens de lista emem outro lugar
Aqui estão dois links que descrevem uso semelhante:
Responder2
Supondo que seja aceitável classificar a tabela Locais, a solução mais simples envolve apenas uma fórmula de validação de dados relativamente curta e alguns intervalos nomeados. Nenhuma coluna ou linha auxiliar é necessária.
Configure duas planilhas de exemplo Locationse Assets, assim:
Adicione dois nomes definidos:
Areas→=Locations!$A:$AIDs→=Locations!$C:$C
Por fim, adicione uma validação de dados de lista suspensa na célula C2na Assetsplanilha com a seguinte fórmula e, a seguir, preencha/copie e cole a célula para baixo:
=INDEX(IDs,MATCH(A2,Areas,0)):INDEX(IDs,MATCH(A2,Areas,0)+COUNTIF(Areas,A2)-1)
As desvantagens deste método são:
- Nomes definidos precisam ser usados
- A tabela Locais deve ser classificada
- Outras tabelas na planilha Locais não podem cruzar a
Areacoluna com segurança, a menos que seja garantido que não contenham valores que correspondam aosAreavalores da tabela Local.
A solução alternativa a seguir supera as desvantagens, exceto a das outras tabelas. No entanto, ele usa células na planilha Ativos para armazenar os itens das listas suspensas.
A planilha Ativos é configurada com colunas adicionais:
Desta vez, a C2fórmula de validação de dados é:
=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))))))))
Insira esta fórmula D2e 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 de múltiplas células - insira esta última fórmula nas células começando E2e indo até a direita até o número máximo de locais esperados em uma área (para o exemplo mostrado que 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()
)
)