Olá Gustavo, tudo bem? Espero que sim!

Desculpe pela demora em retornar.

Você pode utilizar o caractere '*' como um caractere curinga para representar todas as outras abas da pasta de trabalho ao mesmo tempo. Sabendo disso, você pode utilizar a fórmula =SOMA('*'!A1), dessa forma o Excel vai transformar o '*' na referência de todas as planilhas com exceção da planilha que se encontra.

Supondo que exista na pasta de trabalho 5 Planilhas, nomeadas de Planilha1 até Planilha5. Ao escrever a fórmula =SOMA('*'!A1) em alguma célula da Planilha1, o Excel transformará a fórmula para SOMA('Planilha2:Planilha5'!A1), realizando então a soma de todas as células A1's das outras abas.

Só possui um pequeno problema, ao criar uma nova aba, essa fórmula não capta a informação da nova aba, uma vez que está fazendo referência somente às abas já existentes. Então se necessitar de criar novas abas, somente com essa solução não é suficiente.

Uma forma de contornar esse problema é criando um Evento no momento em que é criada uma nova aba, podemos fazer isso utilizando o VBA. Esse evento será responsável por atualizar a fórmula assim que criamos uma nova aba na pasta de trabalho.

Para criarmos esse evento, primeiramente precisamos abrir o Visual Basic, pressionando o atalho Alt + F11, ou abrindo através da aba de desenvolvedor.

Selecione EstaPastaDeTrabalho na seção de VBAProject ( 1 ) e selecione a opção Workbook na seção de scripts do VBA ( 2 ). Feito isso, selecione o evento NewSheet na seção de eventos do VBA. Será criada uma Sub no editor de script, que será executado sempre que uma nova aba for criada na pasta de trabalho do Excel.

O script que será utilizado para esse evento é o seguinte:

Private Sub Workbook_NewSheet(ByVal Sh As Object)

aba_criada = ActiveSheet.Name

Worksheets("Planilha1").Activate
Range("A1").Formula = "=SUM('*'!A1)"
Sheets(aba_criada).Select
End Sub

Primeiro será armazenada a informação da nova aba que será criada na variável aba_criada. Selecionaremos a "Planilha1" que é o nome da planilha onde será colocada a função e colocaremos na célula A1, através de Range("A1").Formula = "=SUM('*'!A1)". Você pode alterar Planilha1 e A1 para o nome que desejar e a célula de interesse. Ao final da macro, a planilha que foi criada será selecionada para que não atrapalhe o fluxo de trabalho.

A seguir você pode conferir o funcionamento da função e pode ver que funciona automaticamente quando uma nova aba é criada, pois o evento é executado e atualiza a função sem atrapalhar o fluxo de trabalho.

Espero que tenha tirado sua dúvida.

Estou à disposição. Bons estudos!