Como corrijo a referência de célula externa no Excel-2007?

Question

Eu tenho dois arquivos spreadsheat ( D: \ Book1.xlsx e D: \ Book2.xlsx ). A primeira pasta de trabalho possui uma tabela, Tabela1 com três colunas (id, nome, valor). No segundo livro de trabalho eu quero valores de referência em Tabela1 usando VLOOKUP . Minha fórmula atual para referenciar valores é

=VLOOKUP(1,'D:\Book1.xlsx'!Table1[#Data],3)

O que funciona quando as duas pastas de trabalho estão abertas. No entanto, se eu abrir Book2.xlsx por si só, a fórmula acima será avaliada como REF #! e a fórmula terá um caminho absoluto em vez do caminho relativo.

Assim que eu abro Book1.xlsx , a referência de caminho torna-se relativa e a fórmula é avaliada como o valor correto.

Então, eu queria saber como faço para que a referência externa funcione sem ter que abrir as duas pastas de trabalho (ou isso é possível)?

Nota

1. Para deixar claro, minha fórmula retorna #REF! quando a segunda pasta de trabalho é aberta sozinha.

2. As duas pastas de trabalho residem na raiz da minha partição D como d: \ Book1.xlsx e d: \ Book2.xlsx.

Answer 1

Eu tive esse problema antes, mas enquanto recriava seu exemplo, tudo estava funcionando bem, então tinha que haver alguma coisa. Acho que posso ter encontrado:

Ao selecionar intervalos externos, o Excel às vezes tem a tendência de revertê-los para grandes referências como esta:

=VLOOKUP(1,[Workbook1.xlsx]Sheet1!$A$4:[Workbook1.xlsx]Sheet1!$A$10,1,TRUE)

Isso funciona bem enquanto duas pastas de trabalho estão abertas, mas realmente não há necessidade de especificar a origem nas duas vezes no intervalo, então pode muito bem ser reduzido para isso:

=VLOOKUP(1,[Workbook1.xlsx]Sheet1!$A$1:$A$10,1,TRUE)

Ainda melhor, você também pode usar intervalos nomeados:

=VLOOKUP(1,[Workbook1.xlsx]Sheet1!MyRange,1,TRUE)

Surpresa, surpresa! Se o Workbook2 for aberto sozinho, o formulário longo mostrará #REF, enquanto a versão mais curta e a versão do intervalo nomeado serão atualizadas corretamente.

    

Answer 2

Eu li em algum lugar que funções que requerem um intervalo, por exemplo, VLOOKUP (), retornarão #REF quando o arquivo de origem for fechado. Consegui contornar adicionando uma guia no arquivo de destino e replicando a tabela de origem por meio de uma referência direta à tabela de origem. Em seguida, faço referência a essa tabela replicada na fórmula de vlookup (que agora está na mesma pasta de trabalho) e tudo funcionou bem.

    

Answer 3

Isso me parece um bug no Excel. Posso reproduzir o comportamento também, mas tenho todas as opções relacionadas a dados externos ativados e válidos. Isso inclui todas as opções nas Configurações da Central de Confiabilidade e todas as referências de pastas de trabalho listadas em Links Externos. A atualização manual de valores de Links Externos também não o faz.

Também segui as instruções deste artigo de suporte da MS . Isso funciona até certo ponto, mas tudo se reverte ao comportamento que você nota depois que eu fecho.