Excel puxar dados para outra aba utilizando fórmula matricial

[mcoumiotis]

Bom dia a todos!

 

Estou precisando puxar dados para outra aba da planilha, e retornar os resultados com fórmula matricial para seguir o padrão da planilha que está toda trabalhada desta forma.

 

Tentei por muito tempo uma solução para isso, mas não cheguei a lugar nenhum.

 

Alguém consegue me ajudar?

 

Segue modelo em anexo e fica aqui meu muito obrigado!

Plan Modelo.xlsx

[Midori]

@mcoumiotis Para retornar a matriz de totais das colunas ímpares (por causa das mesclas), veja se é isto,

 

=ÍNDICE(TRANSPOR('DESCRITIVO DETALHADO'!C13:Z13);LIN(INDIRETO("1:"&LIN('DESCRITIVO DETALHADO'!C13:Z13)-1))*2-1)

 

[mcoumiotis]

Salve @Midori sempre você ajudando!

 

O que ocorre é que tem 3 abas de HISTÓRICO CONSOLIDADO (F1, F2 e F3) e na primeira aba (DESCRITIVO DETALHADO) tem 3 grupos de tabelas, cada grupo desses se refere a um fornecedor - F1, F2 e F3.

 

Preciso que ele faça a busca do fornecedor específico de acordo com as 3 tabelas que estão na aba DESCRITIVO DETALHADO e retorne os resultados dos totais nas 3 outras abas de HISTÓRICO CONSOLIDADO.

 

Justamente para facilitar ele puxar a informação, coloquei referências aos fornecedores na linha dos totais, veja as células B13, B28 e B43. E logo abaixo dos totais, coloquei a referência do Mês/Ano também para facilitar na hora de puxar os dados.

 

Conseguiu entender ou ficou confuso?

[Midori]

@mcoumiotis A fórmula é do fornecedor F1 e portanto deve ficar na aba Histórico F1. Para os outros é só editar o range.

[mcoumiotis]

@Midori certo, porém esse range é dinâmico. A linha de resultados do Fornecedor F1 não é necessariamente a 13, porque estão sempre entrando novas linhas de produtos e jogando os totais pra baixo. Daí a minha dificuldade em fazer uma fórmula para puxar essa linha.

[Midori]

@mcoumiotis Para identificar a linha podemos usar alguma referência do fornecedor na planilha consolidado. Peguei o título da tabela onde tem o número do fornecedor e apliquei na busca com wildcard  "Total Fn*" (n é o número). Se a planilha original tiver outro padrão, modifique Corresp,

 

=ÍNDICE(TRANSPOR(INDIRETO("'DESCRITIVO DETALHADO'!C" &CORRESP("Total F"&DIREITA(B2;1)&"*";'DESCRITIVO DETALHADO'!B:B;0)& ":Z"&CORRESP("Total F"&DIREITA(B2;1)&"*";'DESCRITIVO DETALHADO'!B:B;0)));LIN(INDIRETO("1:"&LIN(INDIRETO("'DESCRITIVO DETALHADO'!C" &CORRESP("Total F"&DIREITA(B2;1)&"*";'DESCRITIVO DETALHADO'!B:B;0)&":Z"&CORRESP("Total F"&DIREITA(B2;1)&"*";'DESCRITIVO DETALHADO'!B:B;0)))-1))*2-1)

 

[mcoumiotis]

@Midori está chegando perto, mais uma vez agradeço sua boa vontade!

 

O que ficou faltando é puxar dados de outros anos, pois na aba DESCRITIVO_DETALHADO embora eu só tenha colocado no exemplo as 3 tabelas dos Fornecedores F1, F2 e F3, todas elas referentes ao ano de 2023, entram nessa mesma aba outras tabelas com os resultados de vários outros anos.

 

Fiz outro modelo para você entender caso eu não tenha explicado bem, coloquei um novo anexo aqui. A diferença é que incluí uma quarta tabela em DESCRITIVO_DETALHADO com os resultados do Fornecedor 1 em 2024.

 

Na aba HISTÓRICO_CONSOLIDADO_F1, preciso que ele me retorne uma matriz que vai de D6 a D29, pois esse é o tamanho das demais colunas.

 

Eu cheguei na seguinte fórmula:

=PROCX(DESLOC(B6;;;CONT.VALORES(B6:B9999));INDIRETO("DESCRITIVO_DETALHADO!C" & CORRESP("Total F1 - " & 2023;DESCRITIVO_DETALHADO!$B:$B;0)+1 & ":Z" & CORRESP("Total F1 - " & 2023;DESCRITIVO_DETALHADO!$B:$B;0)+1);INDIRETO("DESCRITIVO_DETALHADO!C" & CORRESP("Total F1 - " & 2023;DESCRITIVO_DETALHADO!$B:$B;0) & ":Z" & CORRESP("Total F1 - " & 2023;DESCRITIVO_DETALHADO!$B:$B;0));0)

 

Aparentemente, funcionou bem, o problema dela é que onde eu coloquei o "2023" puro, preciso puxar o ano real, você conseguiria adaptar para mim?

 

Basicamente, substituir essas 4 ocorrências do "2023" na fórmula acima por uma fórmula que puxe o ano específico. É o ano de acordo com a coluna B do HISTÓRICO_CONSOLIDADO_F1

 

Muito obrigado pela ajuda!

Plan Modelo.xlsx

[Midori]

@mcoumiotis A fórmula pega o mês/ano até a linha 29, para deixar dinâmico você pode acrescentar uma fórmula para contar as linhas,

 

=ÍNDICE(DESCRITIVO_DETALHADO!B:Z;CORRESP("Total F" &DIREITA(B$2;1)& " - "&ANO(B6:B29);DESCRITIVO_DETALHADO!$B:$B;0);MÊS(B6:B29)*2)

 

[mcoumiotis]

@Midori matou a pau!

 

Impressionante como a solução foi direta e certeira, com uma fórmula pequena que traz exatamente o que precisava.

 

Fiz os testes e funcionou perfeitamente.

 

Mais uma vez obrigado pela boa vontade!

 

Abs