Guru do Excel e Power BI

Enviado: **22 Mar 2022 às 16:34**

Boa Tarde!
A função INDICE utilizada coluna “M” com o intuito de exibir dados correlacionados, esta funcionando parcialmente, ou seja, só funcionam para o primeiro valor pesquisado, se possível, solicito uma análise para verificar onde estou errando.
OBS: Segue planilha anexo com maiores detalhes
Desde já agradeço

Enviado: **22 Mar 2022 às 23:56**

Boa noite.
A coluna auxiliar M faz referência absoluta à primeira célula do intervalo, no caso A4. E a fórmula na validação de dados na coluna B por sua vez está atrelada à coluna M. Por isso só funciona para a primeira linha.
Para listar dados referentes às linhas seguintes, A5, A6, etc, teria que haver uma coluna auxiliar para cada linha, a coluna N para A5, a coluna O para A6, e assim por diante.
Isso é possível, mas não sei se seria aceitável no seu projeto. É possível que alguém neste fórum proponha uma solução melhor via VBA.

Adaptada a sua fórmula, conforme abaixo, em M4 e arrastada para a direita até AN4 e para baixo até a linha 31, por exemplo, irá gerar tantas colunas auxiliares quanto necessárias para o que for colocado no intervalo A4:A31.

Código: Selecionar todos

=SEERRO(SE(ÍNDICE($A$4:$A$31;COLS($A$1:A$1))="";" ";ÍNDICE('BANCO DE DADOS'!$B$9:$B$100000;AGREGAR(15;6;(LIN($B$9:$B$31)-LIN($B$9)+1)/('BANCO DE DADOS'!$A$9:$A$100000=ÍNDICE($A$4:$A$31;COLS($A$1:A$1)));LINS($A$1:A1))));" ")

E também ajustando a sua fórmula na validação de dados em B4, marcando a opção "Aplicar alteração a todas as células com as mesmas configurações":

Código: Selecionar todos

=DESLOC($M$4;;LINS($A$1:$A1)-1;CONT.SE(ÍNDICE(M$4:$AN$31;0;LINS($A$1:$A1));"<> ");1)

Good luck!

Enviado: **24 Mar 2022 às 11:34**

Bom Dia! Prezado Estevaoba - 23 Mar 2022 às 00:56
Primeiramente Muito Obrigado Por Dispor de Seu Tempo Para Ajudar-me.
Sua solução atende, porém o range da coluna “B” está limitado a somente 31, ou seja, quando escolhemos uma variável na célula “A32” não existe um correspondente na célula “B32”, até tentei ajustar (conforme planilha anexo), mas não consegui.
Poderia por obséquio analisar? o ideal é que o limite na coluna “B” passasse dos atuais 31 para 2000.
Desde já Agradeço

Enviado: **24 Mar 2022 às 18:56**

Boa noite. De nada, amigo.

Como falei, não creio que essa solução com fórmulas seja viável no seu caso, com 2000 linhas de validação de dados.
Mas, se quiser tentar, para gerar mais colunas auxiliares até a linha 2000, basta arrastar para a direita a fórmula da célula AN4 até BYJ4 e depois arrastar para baixo.

Ab.

Enviado: **25 Mar 2022 às 15:34**

Prezado Estevaoba - 24 Mar 2022 às 19:56
Obrigado por dispor de seu tempo para analisar a solicitação.
Sua dica funcionou, mas como previsto a planilha ficou um pouco lenta para analise de 2000 linhas, porém neste momento o importante é estar utilizando para fazer as devidas inclusões.
OBS: Devido a lentidão da planilha, vou seguir sua sugestão anterior e encaminhar para um outro fórum de tratativa de problemas via macro, em havendo alguma manifestação/solução te aviso, de qualquer forma utilizaremos a sua tratativa via função até alguma manifestação via macro.
Muito Obrigado
Atenciosamente

Enviado: **25 Mar 2022 às 19:48**

Boa noite amigo.
Como o @Estevaoba, mencionou, essa correspondência por fórmula, para essa quantidade de informações ficará pesada a planilha.
Não entendi direito o filtro que deseja fazer, explica melhor por favor, posso te ajudar com a rotina.

Enviado: **29 Mar 2022 às 09:49**

Bom Dia, Prezado PHSabater - 25 Mar 2022 às 20:48
Primeiramente muito Obrigado Pelo Interesse em analisar.
Existe na planilha “BANCO DE DADOS” uma relação de responsáveis e normas.
O objetivo do trabalho é na planilha “BANCO DE IMAGEM”, quando se escolhe na coluna “A” uma determinada variável, que na coluna “B” sejam exibidas somente as variáveis correspondentes, por exemplo: caso na coluna “A” seja escolhido a variável CELIA, que na coluna “B” sejam exibidas somente as variáveis: NBR7190, NBR7199 , NBR16752 e assim sucessivamente
Nosso amigo @Estevaoba elaborou uma rotina que funciona perfeitamente, porém como já era de se esperar devido ao número de dados, a planilha ficou lenta.
Sendo assim, caso haja uma solução para tornar o processo mais dinâmico, seria bem vindo.
OBS: segue anexo planilha atual
Atenciosamente

Enviado: **30 Mar 2022 às 16:10**

@Saulo , veja se atende.

Instale uma cópia do código abaixo no módulo da planilha BANCO DE IMAGEM.

Código: Selecionar todos

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim r, LR As Long
  If Target.Count > 1 Then Exit Sub
  If Target.Column > 1 Then Exit Sub
  Application.ScreenUpdating = False
  If Target.Value = "" Then
   Target.Offset(, 1).Value = "": Target.Offset(, 1).Validation.Delete
  Else: [W:W] = ""
   With Sheets("BANCO DE DADOS")
    On Error Resume Next
    .ShowAllData
    On Error GoTo 0
    LR = .Cells(Rows.Count, 1).End(3).Row
    .Range("A8:F" & LR).AutoFilter 1, Target.Value
    .Range("B10:B" & LR).Copy: [W1].PasteSpecial xlValues
    .ShowAllData
    r = Application.Transpose(Range("W1:W" & Cells(Rows.Count, 23).End(3).Row))
    Target.Offset(, 1).Value = "": Target.Offset(, 1).Validation.Delete
    If Application.CountA([W:W]) > 1 Then
     Target.Offset(, 1).Validation.Add Type:=xlValidateList, Formula1:=Join(r, ",")
    Else: Target.Offset(, 1) = [W1]
    End If
   End With
   [W:W] = ""
  End If
End Sub

#
funcionamento - planilha BANCO DE IMAGEM
1. ao limpar uma célula da coluna A, o código irá limpar o conteúdo e a Validação de B, mesma linha
2. ao inserir um nome na coluna A, o código irá limpar o conteúdo e irá inserir a Validação em B caso haja mais de uma Norma para o nome inserido, se não, irá inserir a Norma em B, mesma linha

#
dica - para não exibir linhas vazias na Validação da coluna A, altere a fórmula que nomeia o Intervalo SUMARIO_COLABORADOR, conforme em vermelho abaixo.
atual ~~~>='BANCO DE DADOS'!$F$10:ÍNDICE('BANCO DE DADOS'!$F$10:$F$1048576;CONT.SE('BANCO DE DADOS'!$F$10:$F$1048576;">0"))
alterada ~~~> ='BANCO DE DADOS'!$F$10:ÍNDICE('BANCO DE DADOS'!$F$10:$F$1048576;CONT.SE('BANCO DE DADOS'!$F$10:$F$1048576;">"""))

Enviado: **31 Mar 2022 às 14:55**

Boa tarde Prezado osvaldomp - 30 Mar 2022 às 17:10
Obrigado por analisar/resolver a essa demanda.
A solução superou e superou em muito ao esperado.
DEUS lhe pague.

Guru do Excel e Power BI

[RESOLVIDO] Função (INDICE) só retorna o primeiro valor

[RESOLVIDO] Função (INDICE) só retorna o primeiro valor

Re: Função (INDICE) só retorna o primeiro valor

Re: Função (INDICE) só retorna o primeiro valor

Re: Função (INDICE) só retorna o primeiro valor

Re: Função (INDICE) só retorna o primeiro valor

Re: Função (INDICE) só retorna o primeiro valor

Re: Função (INDICE) só retorna o primeiro valor

Re: Função (INDICE) só retorna o primeiro valor

Re: Função (INDICE) só retorna o primeiro valor