Как исправить ссылку на внешнюю ячейку в Excel-2007?

Question

У меня есть два файла электронных таблиц (D:\Book1.xlsx и D:\Book2.xlsx). Первая рабочая книга имеет таблицу Table1 с тремя столбцами (id, Name, Value). Во второй рабочей книге я хочу, чтобы ссылочные значения в Таблице 1 использовались с помощью VLOOKUP. Моя текущая формула для ссылки на значения

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

Что работает, когда обе рабочие книги открыты. Однако, если я открою Book2.xlsx сам по себе, приведенная выше формула оценивается как REF #! и формула имеет абсолютный путь вместо относительного пути.

Как только я открываю Book1.xlsx, ссылка на путь становится относительной, и формула получает правильное значение.

Поэтому мне было интересно, как получить внешнюю ссылку для работы без необходимости открывать обе рабочие книги (или это возможно)?

Заметка

1. Чтобы быть понятным, моя формула возвращает #REF! когда вторая рабочая тетрадь открывается сама по себе.

2. Обе книги находятся в корне моего раздела D как d:\Book1.xlsx и d:\Book2.xlsx.

Answer 1

У меня была эта проблема раньше, но при воссоздании вашего примера все работало нормально, так что должно было быть что-то. Я думаю, что, возможно, нашел это:

При выборе внешних диапазонов Excel иногда имеет тенденцию возвращать их к огромным ссылкам, например так:

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

Это прекрасно работает, пока открыты 2 книги, но на самом деле нет необходимости указывать источник оба раза в диапазоне, поэтому его вполне можно сократить до следующего:

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

Более того, вы также можете использовать именованные диапазоны:

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

Сюрприз Сюрприз! Если Workbook2 открыт один, в длинной форме отображается #REF, а более короткая версия и версия с указанным диапазоном обновляются аккуратно.

Answer 2

Я где-то читал, что функции, которые требуют диапазона, например, VLOOKUP(), будут возвращать #REF, когда исходный файл закрыт. Я смог обойти эту проблему, добавив вкладку в целевой файл и реплицировав исходную таблицу с помощью прямой ссылки на исходную таблицу. Затем я ссылаюсь на эту реплицированную таблицу в формуле vlookup (которая теперь находится в той же книге), и все работало хорошо.

Answer 3

Это выглядит для меня как ошибка в Excel. Я также могу воспроизвести поведение, но у меня есть все опции, связанные с внешними данными, которые включены и действительны. Это включает в себя все параметры в настройках центра управления безопасностью и все ссылки на книги, перечисленные во внешних ссылках. Обновление значений из внешних ссылок вручную также не делает этого.

Я также следовал инструкциям в этой статье MS Support. Это работает до некоторой степени, но все возвращается к тому поведению, которое вы заметили после моего закрытия.