Infelizmente, acho que os arquivos de destino precisam estar abertos quando você tiver a chance do arquivo de origem.
Eu me deparei com esse problema exato de ter um arquivo de origem central e um grande número de arquivos dependentes. Alterei algumas das colunas no arquivo de origem com os arquivos de destino fechados e meus arquivos de destino perderam a sincronização com ele.
Uma coisa que você pode tentar se estiver usando o Excel 2007 ou superior e, se estiver usando a pesquisa, colocar os dados de origem em uma tabela e referir-se aos nomes das colunas / linhas nos arquivos de destino. Nunca tentei isso, mas pode funcionar. Também simplifica muitas fórmulas.
Portanto, em vez de alimentar um intervalo como A: 100, A: 1000, você acabou de fornecer Tabela1 ("Data") ou algo similar.
EDIT: Vendo como você marcou isso com o Excel 2003, isso não será uma solução viável para você, que eu saiba. Quando salvo minhas tabelas no formato do Excel 2003, recebo avisos sobre compatibilidade com referências de tabela.
Na verdade, uma solução ideal seria migrar para um banco de dados do Access, pois isso é muito melhor em relacionar dados em tabelas diferentes