INDIRECT dá um #REF! erro para referência a pasta de trabalho externa onde fazer uma referência direta com um sinal de igual funciona. Por quê?

Question

Eu tenho uma referência a outra pasta de trabalho que se parece com isso:

 =INDIRECT(ADDRESS(21,6,1,1,"[Longterm Budget.xlsx]2012"))

O resultado desse argumento INDIRECT, ADDRESS(21,$A$1,1,1,"[Longterm Budget.xlsx]2012") , é '[Longterm Budget.xlsx]2012'!$F$21 . Se eu colocar  um sinal de igual na frente daquele

 ='[Longterm Budget.xlsx]2012'!$F$21

... devolve a referência desejada. No entanto, passando esse mesmo  argumento para INDIRECTA assim

 =INDIRECT("'[Longterm Budget.xlsx]2012'!$F$21")

... resulta em um erro de referência. Alguma ideia do porquê?

    

Answer 1

INDIRECT infelizmente só funciona com a pasta de trabalho ou com pastas de trabalho que estão abertas durante o tempo de cálculo. Se a pasta de trabalho externa estiver fechada, isso resultará em um erro #REF! .

Segundo plano: Para links externos normais, o Excel armazena não apenas o endereço da célula externa, mas também o último valor conhecido na versão salva do arquivo. Dessa forma, ao abrir o arquivo, o Excel pode calcular a pasta de trabalho inteira, mesmo que a fonte externa não esteja disponível.

INDIRECT no entanto é uma função volátil , ou seja, o Excel precisa calculá-la toda vez que um cálculo é executado (enquanto uma função "normal" só precisa ser recalculada se qualquer célula predecessora mudar ). Portanto, o Excel não pode armazenar o valor da referência e, portanto, resulta no erro #REF! .

    

Answer 2

OK, acabei de postar isso no StackOverflow ... Espero que isso não seja postagem dupla ...

Aqui está um método de dinossauro para você no Office 2010.

Escreva o endereço completo desejado usando concatenar (o método "&" de combinar texto).

Faça isso para todos os endereços de que você precisa. Deveria se parecer com:

="=" & '"\ ENDEREÇO DE REDE COMPLETO incluindo [Nome da planilha]" & W3 & "'! $ w4"

O W3 é uma referência dinâmica para qual folha eu estou usando, o W4 é a célula que eu quero pegar da planilha.

Depois de ter isso, inicie uma sessão de gravação de macro. Copie a célula e cole-a em outra. Eu colei em uma célula mesclada e me deu o erro clássico "Same size". Mas uma coisa que fiz foi colar o texto resultante da minha concatenação (incluindo o extra "=").

Copie mais do que muitos para os quais você fez isso. Então, entre em cada célula colada, selecione o texto e aperte enter. Ele atualiza para uma referência direta ativa.

Quando terminar, coloque o cursor em algum lugar e pare a macro. Atribua-o a um botão e está pronto.

É um pouco PITA fazer isso pela primeira vez, mas depois de ter feito isso, você acabou de fazer o pino quadrado encaixar no buraco redondo.