Aqui está uma abordagem para criar listas dinâmicas de coisas no Excel.
Suponha que você tenha uma matriz dos números de linha em que seus critérios são TRUE (por exemplo, Código do comprador = JDH e Remanescente > 0). Em seguida, você poderia alimentar essa matriz com uma função SMALL() que listaria os números de linhas quando eles fossem preenchidos. E, finalmente, você poderia usar o SMALL() como a função row_nums em um INDEX() , e isso listaria os valores apropriados de qualquer coluna que você especificasse com o parâmetro column_num .
Então, vamos começar obtendo uma matriz dos números das linhas. Para simplificar, presumo que sua tabela de dados tenha apenas 25 linhas e que o Painel esteja na mesma planilha que os dados. Além disso, estou usando o código de comprador ELN, que está na célula B30.
Primeiro, calcularemos uma matriz de valores TRUE/FALSE correspondentes aos seus critérios:
($C$1:$C$25=$B$30)*($G$1:$G$25>0)
A primeira expressão fornece uma matriz de valores TRUE/FALSE onde o Código do comprador (coluna C) = ELN e o segundo fornece uma matriz de valores TRUE/FALSE onde Restante (coluna G) é > 0. Em operações aritméticas envolvendo valores lógicos, TRUE e FALSE são convertidos em 1s e 0s, portanto, essa multiplicação fornece uma matriz de 1s e 0s com 1s nas posições em que seus critérios são ambos TRUE .
Agora, precisamos converter essa matriz em uma lista de números de linha em que seus critérios são TRUE . Se dividirmos uma matriz de números sequenciais por essa matriz de 1s e 0s, obteremos uma matriz dos números de linha sempre que houver 1, intercalados com erros de #DIV/0! sempre que houver um 0.
ROW($1:$25)/(($C$1:$C$25=$B$30)*($G$1:$G$25>0))
Em seguida, em vez de SMALL() , usaremos AGGREGATE() , que faz a mesma coisa que SMALL() faria (função = 15), exceto que tem uma opção para ignorar erros (opção = 6). Também AGGREGATE() pode manipular arrays sem ter que entrar com CTRL Deslocar Enter .
Portanto, na fórmula abaixo, AGGREGATE() ignora os erros #DIV/0! e fornece uma matriz contendo apenas os números das linhas que correspondem aos seus critérios:
AGGREGATE(15,6,ROW($1:$25)/(($C$1:$C$25=$B$30)*($G$1:$G$25>0)),ROW()-33)
Eu tenho esta fórmula na linha 34, então o "-33" faz a função começar em 1 e listar os números das linhas conforme elas são preenchidas. Altere este parâmetro para se adequar à sua situação.
Agora podemos usar isso em um INDEX () e agrupar tudo em IFERROR() para inserir espaços em branco quando INDEX() ficar sem row_nums quando estiver cheio. Esta fórmula, preenchida e à direita de B34:
=IFERROR(INDEX($A$1:G$25,AGGREGATE(15,6,ROW($1:$25)/(($C$1:$C$25=$B$30)*($G$1:$G$25>0)),ROW()-33),MATCH(B$32,$A$1:$G$1,0)),"")
apresenta os resultados mostrados abaixo.
ParaoCompradorELN,apenasaslinhas5e11têm0Restanteseafórmulalistatodasaslinhas,excetoas.
ObservequemereferiaoscabeçalhosdopainelnoMATCH()queforneceocolumn_num'susandoB$32comolookup_value.Quandoafórmulaépreenchida,issofuncionaparaonúmerodopedidoeadescrição,masosoutrostítulosnopainelnãosãoosmesmosdatabeladedados.
Portanto,paraasoutrascolunas,substituíareferênciadecélulaB$32pelotextocitado:"LINE_NBR" para a coluna Linha de PO, "QUANTITY" para a coluna Quantidade solicitada e "REMAINING" para a coluna Quantidade recebida. Esta última coluna envolve um cálculo, portanto, a fórmula é, na verdade:
=E34-IFERROR(INDEX($A$1:G$25,AGGREGATE(15,6,ROW($1:$25)/(($C$1:$C$25=$B$30)*($G$1:$G$25>0)),ROW()-33),MATCH("REMAINING",$A$1:$G$1,0)),"")
A última coluna, "Necessário", é calculada como:
=E34-F34 e preenchido.
Espero que isso ajude e boa sorte.