Guru do Excel e Power BI

Pessoal, tenho uma dúvida que desejava ver se me podem ajudar:

1. Numa sheet tenho uma listagem de dados sobre fornecedores, pagamentos e facturações, que abrange mais de uma centena de linhas e algumas 12 colunas.

2. Numa outra sheet, tenho um modelo de facturas onde muitos dos dados estão sendo buscados da 1ª sheet, através de fórmulas simples de =!Folha1C3... D3, E3, etc.

3. O problema é que ao arrastar o modelo da factura pela sheet abaixo de modo a ter uma factura por cada linha/fornecedor da 1ª Sheet, estas fórmulas não passam de =!Folha1C3... D3, E3 para =!Folha1C4... D4, E4 mas sim para de =!Folha1C28... D28, E28, conforme o número de linhas de intervalo entre as células arrastadas.

Será que conseguem perceber a questão? Não tenho um documento que possa publicar, por isso peço desculpa, mas se for preciso improviso um.

Abraço,
João

Para facilitar a criação e teste de fórmulas, favor postar uma planilha exemplo com algumas linhas da sua planilha real, dados fictícios, e coloque manualmente o resultado esperado, explicando por que ele deve ser assim.
Clique em +Resposta, Adicionar um anexo.

Ab.

Boas,

Segue então um exemplo fictício do excel que descrevi. Na folha 2, destaquei a verde na 1ª factura as fórmulas que vão buscar informação da folha 1, no caso à linha 2 que também está a verde.

O meu objectivo é conseguir uma forma que permita que as facturas da folha 2 busquem correctamente a informação da folha 1, tendo em consideração que o excel original tem centenas de linhas na folha 1 e portanto terei de ter centenas de facturas na folha 2. Não dá para corrigir as fórmulas

Julgo que a forma mais natural será arrastar a 1a factura pela folha abaixo, mas não consigo fazer com que as fórmulas saltem apenas uma linha da informação da folha 1.

Resumindo: o que quero é que as células da folha 2 correspondam à informação da folha 1, conforme as cores.

Olhando ao excel será mais fácil

Abraço

Fiz usando a função DESLOC com a seguinte fórmula em C9:
Copie para os outros campos ajustando a referência para Folha1!$D$2 e para o intervalo de CONT.SE(D$7:D9;$D$7).

Depois copie o intervalo B1:E25 da primeira fatura e cole em B26 para a segunda fatura, e assim por diante.

Confira no anexo, faça testes e veja se atende.

Good luck!

Viva,

Muito obrigado.
Não sei se estou a fazer algo mal, mas as fórmulas dão erro.
Aparentemente, as fórmulas estão iguais ao seu excel, mas dá-me a mensagem #NOME?
Deixo o meu excel em anexo (já com as fórmulas que me deu) para ver se consegue ajudar-me a identificar o problema.

Obrigado.
João

De nada, João. É um prazer ajudá-lo.

No seu último arquivo, somente a fórmula em C9 estava correta:

=DESLOC(Folha1!$D$2;CONT.SE(D$7:D9;D$7)-1;0)
Observe que, como o texto "Data" sempre ocorre no seu Recibo, utilizo a repetição dessa ocorrência com a função CONT.SE para variar na função DESLOC o incremento de linha na planilha com dados.

Mas é preciso que o intervalo de CONT.SE sempre comece na célula D7 e termine na linha da célula atual.
Por exemplo, seu erro na célula C14 foi iniciar o intervalo em D12, então retornou erro, pois nesse intervalo não há ocorrência de "Data": =DESLOC(Folha1!$D$2;CONT.SE(D$12:D14;D$12)-1;0)

O correto em C14 é =DESLOC(Folha1!$H$2;CONT.SE(D$7:D14;D$7)-1;0) e aumentando o intervalo D$7:D14 à medida que avance para as outras linhas.

Todavia, como DESLOC é uma função volátil, às vezes há possibilidade de erros, então criei esta outra fórmula com funções não voláteis:
Observe que uso a função QUOCIENTE para determinar a linha a ser buscada na aba com dados:
QUOCIENTE(LIN();25)+1. Em C9, no caso, QUOCIENTE(LIN();25) = QUOCIENTE(9;25)=0, por isso acrescento +1, para retornar o valor 1, ou seja, a primeira linha da aba com dados.

Quando você arrastar as fórmulas para o próximo recibo, C9 se tornará C34, logo, QUOCIENTE(LIN();25) = QUOCIENTE(34;25)=1, e +1, retornará o valor 2, ou seja, a segunda linha da aba com dados.
Assim sucessivamente.

Para os outros campos, ajuste a coluna na fórmula, por exemplo:
Favor conferir no anexo.
Espero que funcione desta vez.

Ab.

Boas,

Muito obrigado. Agora sim, problema resolvido
A outra solução dava-me a mensagem #NOME? mesmo na célula C9, que supostamente estava bem.
Com esta está tudo ok e já apliquei no ficheiro original.

Mais uma vez, muito obrigado.
Grande abraço,
João