INDIRECT дает #REF! ошибка для ссылки на внешнюю рабочую книгу, где работает прямая ссылка со знаком равенства. Зачем?

Question

У меня есть ссылка на другую книгу, которая выглядит так:

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

Результатом этого НЕПРАВИЛЬНОГО аргумента, ADDRESS(21,$A$1,1,1,"[Longterm Budget.xlsx]2012") , является '[Longterm Budget.xlsx]2012'!$F$21 Если я поставлю знак равенства перед этим

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

... возвращает нужную ссылку. Тем не менее, передавая тот же аргумент в опосредованный

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

... приводит к ошибке ссылки. Есть идеи почему?

Answer 1

INDIRECT к сожалению, работает только с рабочей книгой или с рабочими книгами, открытыми в течение времени расчета. Если внешняя рабочая книга закрыта, это приведет к #REF! ошибка.

Фон: для обычных внешних ссылок Excel сохраняет не только адрес внешней ячейки, но и последнее известное значение в сохраненной версии файла. Таким образом, при открытии файла Excel может рассчитать всю книгу целиком, даже если внешний источник недоступен.

INDIRECT однако, является изменчивой функцией, то есть Excel должен вычислять ее каждый раз, когда выполняется вычисление (в то время как "нормальная" функция должна быть пересчитана только в случае изменения какой-либо из предшествующих ячеек). Таким образом, Excel не может сохранить значение ссылки и, следовательно, приводит к #REF! ошибка.

Answer 2

ОК, я только что опубликовал это в StackOverflow ... Я надеюсь, что это не двойная публикация ...

Вот метод динозавра для вас на Office 2010.

Напишите полный адрес, который вы хотите, используя метод конкатенации (метод объединения текста «&»).

Сделайте это для всех адресов, которые вам нужны. Это должно выглядеть так:

= "=" & "'\ АДРЕС ПОЛНОЙ СЕТИ, включая [Имя таблицы]" & W3 & "'!$ W4"

W3 - это динамическая ссылка на тот лист, который я использую, W4 - это ячейка, которую я хочу получить от листа.

После этого запустите сеанс записи макроса. Скопируйте ячейку и вставьте ее в другую. Я вставил его в объединенную ячейку, и он выдал мне классическую ошибку "Тот же размер". Но единственное, что он сделал, это вставил полученный текст из моей конкатенации (включая этот дополнительный «=»).

Скопируйте столько, сколько вы сделали это для. Затем перейдите в каждую вставленную ячейку, выделите текст и просто нажмите Enter. Он обновляет его до активной прямой ссылки.

Как только вы закончите, поместите курсор в нужное место и остановите макрос. Назначьте его кнопке, и все готово.

Это немного PITA, чтобы сделать это в первый раз, но как только вы сделали это, вы только что сделали квадратный колышек подходящим для этой проклятой круглой дыры.