Excel Como incrementar a referência de uma célula de varias abas?

[Luiz C Querido]

Bom dia pessoal. Estou enroscado num problema que aparentemente não deveria ocorrer.

 

Uso o Excel 2016 na empresa.

 

Tenho uma pasta de trabalho "Controle de Entrega" com uma planilha que chamarei de "Controle" e que uso para controle de entrega de comprovantes de pedido.

Os dados nesta eu copio de outras pastas de trabalho porque nestas, os nomes das abas variam, não sendo possível fazer uma referência diretamente. Sendo assim eu crio uma cópia destas para a pasta de trabalho "Controle de Entrega", colo como cópia ao lado da planilha Controle, e altero o nome das abas para Planilha1, Planilha2, etc que creio ser o padrão no Excel.

 

Exemplo: Tenho uma planilha nome Controle e nas células An, Bn, Cn e Dn preciso copiar os dados das planilhas Planilha1, Planilha2, Planilhan,  cujas referencias que preciso de cada são H6, B8, C8 e H12 e não mudam.

 

Sendo assim, se minha referência em Controle começa em A1, B1, C1 e D1 eu teria o seguinte:

Controle A1 = Planilha1 H6

Controle B1 = Planilha1 B8

Controle C1 = Planilha1 C8

Controle D1 = Planilha1 H12

 

Sendo assim, a célula A1 da planilha ficaria parecido com isso aqui

=Planilha1!$H$6

Sendo assim, eu referenciei a linha 1 inteira para o resto.

 

O problema:

Daí pensei, agora é só copiar =Planilha!$L$6 para a linha 2, 3 e 4 e deve incrementar automaticamente para =Planilha2!$L$6, =Planilha3!$L$6 e =Planilha4!$L$6, certo? Errado!

Não incrementa. Tá, beleza, vamos pelo método mais difícil então. Copio e altero eu mesmo  o numero da planilha e ...Não funciona.

O que acontece:

Digamos que a célula H6 das Planilhas 1 e 2 tenham o seguinte

Local        Conteudo
Planilha1 H6    350
Planilha2 H6    500

Logo, se em
Controle A1 =Planilha1!$H$6 = 350
Controle A2 =Planilha2!$H$6 = "=Planilha2!$H$6"

 

Ou seja, a célula ignora a referência e exibe como texto. Parece que a referência ficou vinculada a cópia da célula e não a referência em si. Alguém poderia me ajudar? Não quero mexer com VBA pois na empresa o VBA é bloqueado.

 

Grato

 

 

 

 

 

[Visitante]

 

Experimente:

 

=INDIRETO("'Planilha"&LIN(A1)&"'!H6")

Lembrando que a função INDIRETO() só funciona enquanto o arquivo que contém as planilhas de origem do dados estiver aberto.

[Luiz C Querido]

37 minutos atrás, osvaldomp disse:

 

Experimente:

 

=INDIRETO("'Planilha"&LIN(A1)&"'!H6")

Lembrando que a função INDIRETO() só funciona enquanto o arquivo que contém as planilhas de origem do dados estiver aberto.

 

Não deu certo. A minha célula que deve conter os dados é a D4 que está na planilha Controle

A célula que contém o dado que quero que seja mostrado na D4 da planilha Controle, está na célula L6 da planilha Plan1

 

Fiz o que você disse:

Estava

D4=Plan1!L6

 

Ficou

D4=indireto("'Plan1"&lin(d4)&"'!l6")

 

E o que aparece em D4 é "=indireto("'Plan1"&lin(d4)&"'!l6")" ou seja, a formula apenas, como se ela fosse apenas um texto.

 

[Visitante]

48 minutos atrás, Luiz C Querido disse:

 

Não deu fiz certo.

 

E o que aparece em D4 é "=indireto("'Plan1"&lin(d4)&"'!l6")" ou seja, a formula apenas, como se ela fosse apenas um texto.

Provavelmente D4 está formatada como Texto. Altere para Geral.

 

 

=INDIRETO("'Plan"&LIN(A1)&"'!L6")

a função LIN(A1) irá retornar o número 1, que será utilizado pela função INDIRETO() como o complemento do nome da planilha de origem, ou seja, Plan1, e ao ser arrastada pela coluna irá retornar Plan2, Plan3, ..., independente da planilha e da célula que contém a fórmula.

 

Com base na primeira fórmula que passei, coloque na nova fórmula Plan e não Plan1.

[Luiz C Querido]

1 hora atrás, osvaldomp disse:

 

=INDIRETO("'Plan"&LIN(A1)&"'!L6")

a função LIN(A1) irá retornar o número 1, que será utilizado pela função INDIRETO() como o complemento do nome da planilha de origem, ou seja, Plan1, e ao ser arrastada pela coluna irá retornar Plan2, Plan3, ..., independente da planilha e da célula que contém a fórmula.

 

Com base na primeira fórmula que passei, coloque na nova fórmula Plan e não Plan1.

 

Então, De D4 até D7 deu no seguinte:
 

=INDIRETO("'Plan"&LIN(D4)&"'!$l$6") ficou correto

=INDIRETO("'Plan"&LIN(D5)&"'!$l$6") colocou 0

=INDIRETO("'Plan"&LIN(D6)&"'!$l$6") colocou #REF!
=INDIRETO("'Plan"&LIN(D75)&"'!$l$6") colocou #REF!

 

Mas em todas as abas, o valor da célula estava correto. Foi aí que fui tentar entender o que o bendito D4 significava na função Lin. Entendido isso, efetuei os ajustes e agora está tuudo certinho pois a primeira célula ficou =INDIRETO("'Plan"&LIN(D1)&"'!$l$6").

Daí repliquei para as células adjacentes, colocando o vinculo e deu tudo certo.

 

Mas não entendo porque a referência direta não funciona ou seja, eu crio o vínculo =Plan1!L6 am A1. Se eu editar a formula para =Plan1!L7 ou =Plan2!L6 o vínculo é perdido e mesmo que eu retorne para o valor anterior( não pelo desfazer, digitando o valor mesmo) permanece perdido e nao volta mais.

adicionado 0 minutos depois

Oops, esqueci de agradecer. Muito obrigado Osvaldo!

[Patropi]

Bom dia @Luiz C Querido

 

Você se esqueceu de clicar no Polegar para curtir as respostas do Osvaldo.

 

Ao responder não use o botão Citar, use a janela de resposta que fica logo abaixo da última resposta.

 

Se a dúvida foi sanada, marque o tópico como Resolvido(Na minha assinatura tem um link para uma instrução de como proceder).

 

[]s