При открытии книги формула связанных данных Excel превращается в #REF

Question

Это случалось со мной несколько раз (но не всегда). Это очень расстраивает, и я понятия не имею, почему это происходит.

A.xls содержит формулу, которая ссылается на B.xls Sheet1. Я работаю над A.xls. Я решил открыть B.xls. Как только я это сделаю - все ссылки на Sheet1 превращаются в #REF.

то есть.

='C:\..\[B.xls]!Sheet1'!A1

превращается в

='C:\..\[B.xls]!#REF'!A1

и, естественно, все значения, которые ранее были правильными, теперь возвращают #REF.

Это очень расстраивает, так как я не могу отменить это, не открыв мою последнюю сохраненную версию A.xls. Поиск и замена также не идеальны, потому что A.xls ссылается на несколько различных рабочих листов B.xls - и все они теперь #REF.

Есть идеи почему? :( Запуск Excel 2010.

Answer 1

У меня та же проблема. у меня есть офис 2010. файл Excel хранится на общем диске (NAS).

Решение: выберите «Файл»> «Параметры»> «Центр управления безопасностью». Справа нажмите Настройки центра управления безопасностью. В окне «Центр управления безопасностью» с левой стороны нажмите «Защищенный просмотр», затем с правой стороны снимите флажок «Включить защищенный просмотр» для файлов, исходящих из Интернета.

Теперь мой #REF! задача решена.

Answer 2

Один из способов избежать проблемы:
Используйте INDIRECT() для имен файлов и листов с текстовой ссылкой.

Пример:
Сделайте Z1 'C:\..\[B.xls]!Sheet1' (с кавычками?Непроверенные)
Затем используйте =INDIRECT(Z1&"!A1") где вам нужно сослаться на ячейку A1 на этом листе.

Функция INDIRECT работает, только если у вас открыт дополнительный файл Excel. После того, как закрытые, все эти INDIRECT ссылки становятся #REF.

Answer 3

Решением может быть открытие файла, в котором вы дали ссылки, а затем перейдите в Data > Edit Links > Open Source . Это откроет исходный код без ошибки #ref .

Другой вариант - сохранить две версии со ссылками и без них. Версия без ссылок может быть сделана Data > Edit Links > Break Link .

Я думаю, что это ошибка в Excel, которая делает имя листа #ref .

Answer 4

Чтобы помочь с комментарием Джерри. Когда вы закроете A.xls, он запомнит значения из формулы. Если затем вы в любом случае измените B.xls, например, имя книги или имя листа, то, когда A.xls попытается обновить себя новыми данными, он не может быть найден из-за изменения ссылочного имени.

Скажите, что ваша формула похожа на = '[B.xls] Sheet3'!$ B $ 1588 Если вы измените имя B.xls после закрытия A.xls на «Данные сохранены 08.09.2013», то при открытии A.xls он будет иметь значения из B.xls. Как только вы откроете данные, сохраненные 08.09.2013, A.xls поймет, что данные больше не существуют, и потеряет их.

Чтобы обойти это, убедитесь, что вы обновили ссылки в формуле. Это должно решить все это.

Answer 5

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

Я считаю, что ваша проблема заключается в том, что в B.xls были внесены изменения, и формулы в A.xls пытаются обновить его формулы, когда вы открываете B.xls. Поскольку в B.xls произошли изменения с тех пор, как формулы в A.xls были рассчитаны в последний раз (например, листы были переименованы или просто удалены), формулы в A.xls не могут найти предыдущую ссылку и поэтому возвращают #REF! ,

Я думаю, что вы можете иметь два варианта, чтобы избежать этого:

1. Удалите формулы в A.xls путем копирования / вставки значений. Это предотвратит любые обновления в A.xls от изменений в B.xls и любых других формулах на листе. Это само по себе является недостатком.
2. Убедитесь, что любые изменения в B.xls не включают удаление листа, переименование или удаление строк / столбцов в тех, которые предоставляют формулы в A.xls.

Answer 6

Проблема заключается в центре доверия. Если вы работаете с сетевыми дисками, вы должны установить флажок "разрешить подключения к надежным расположениям". Это позволяет избежать потери ссылки.

Answer 7

Подбирая полезные указатели из вышеперечисленного, это работало для меня с файлами с ссылками на ошибки, когда открывался связанный файл в нашей сети:

Параметры> Центр управления безопасностью> Настройки центра управления безопасностью> Надежные расположения> Установите флажок «Разрешить надежные расположения в моей сети (не рекомендуется)».

Ошибки доверия были исправлены!

Спасибо.

Answer 8

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

Answer 9

Кажется, что ссылки изменены на #REF! потому что целевая электронная таблица (B.xls) не является доверенной. Зайдя в ФАЙЛ-> Параметры-> Центр управления безопасностью-> Настройки центра управления безопасностью-Надежные расположения и добавив папку, содержащую B.xls, я смог остановить проблему. Предположительно, вы могли бы добавить B.xls в "Доверенные документы" и получить тот же результат, но я не запустил этот тест.

Answer 10

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

Проблема была решена, когда я удалил пробел перед именем столбца таблицы, на которую ссылалась формула.

Я не уверен, что проблема такая же, как у вас, но, возможно, помогает ..

Answer 11

Решение состоит в том, чтобы перейти к меню «Файл»> «Параметры»> «Формулы» и снять флажок « Данные, введенные в таблицу, недействительны».