Criei duas planilhas uma com valores de venda mensal, discriminada por itens e no final com o valor do somatório total dos itens.
A segunda planilha, apresenta os valores totais retirados da primeira tabela.

Utilizei o seguinte comando para copiar os valores da primeira planilha: =[Mensal.xlsx]Planilha1!$D$8

O problema acontece quando preciso adicionar mais linhas na primeira planilha. Quando os dois arquivos estão abertos, eu adiciono linhas normalmente para colocar mais itens na primeira planilha e o somatório dos itens funciona certinho, passando o valor total para a segunda planilha.

Porém, quando a planilha anual está fechada e adiciono mais itens na planilha mensal e salvo. Ao abrir a planilha anual e clicar para atualizar o vínculo, ele retira apenas o valor referente a célula D8 da planilha mensal e não mais o valor total, que no caso se tiver adicionado apenas 1 item, estaria agora na D9.

Não sei se ficou bem claro, mas vou anexar as planilhas para entender melhor.