Некоторые формулы на этом листе содержат недопустимые ссылки как исправить - Исправление ошибок и поиск оптимальных решений проблем

Проблему описал

gsheppar

в 2010 году на сайте mrexcel.com (по-английски). Оставлено без ответа.

http://www.mrexcel.com/forum/excel-questions/464779-run-time-error-1004-name-you-entered-not-valid.h…

Проблема «жива» и актуальна для версий Excel: 2010, 2013, 2016 (в 2007 — не проверял).

Сообщение от Excel: «Некоторые формулы на этом листе содержат недопустимые ссылки. Убедитесь, что ссылки на ячейки, имена диапазонов, определенные имена и связи с другими книгами в формулах указаны правильно.»

Штатные инструменты ошибок не видят (надстройка Name Manager, вроде, тоже). Указанные объекты выведены программно и проанализированы. Ошибок нет, но обнаружено, что использование в книге формул, введенных впервые в Excel 2007, приводят к создание имен с префиксом «_xlfn.» (своего рода, маркер):
Name RefersTo RefersToLocal
_xlfn.COUNTIFS =#NAME? =#ИМЯ?
_xlfn.IFERROR =#NAME? =#ИМЯ?

Разумеется, в моей книге использованы соответствующие формулы (значения аргументов в скобках здесь рассматривать не надо):
=СЧЁТЕСЛИМН(КР;B$25;НаимОц10;$A26)
=ЕСЛИОШИБКА(ПЕРСЕНТИЛЬ(ЕСЛИ((КР=B$4)*НЕ(ЕПУСТО(БаллОц10));БаллОц10);0,25);НД())

В Интернете пишут (врут), что это якобы случается, если открыть приложение, использующее новые формулы, в Excel 97-2003, но проверено, что это не так. Советуют также заменить новые формулы старыми решениями: например, вместо ЕСЛИОШИБКА (IFERROR) использовать комбинацию формул ЕСЛИ (IF) и ЕОШИБКА (ISERROR), а формулу СЧЁТЕСЛИМН (COUNTIFS) заменить на формулу СУММАПРОИЗВ (SUMPRODUCT) и т.д. и т.п. Увы, пробовал — заменил, но это не привело к желаемому результату: то самое сообщение продолжает выскакивать, как черт из табакерки: раздражает… Но всё работает. При этом, естественно, имена с префиксом «_xlfn.» исчезают (после сохранения книги и повторного открытия).
Очевидно, что эта проблема как была в Excel 2010, так и осталась в Excel 2016.
При открытии книги всё обстоит нормально, но стоит, например, изменить раз или другой раз, третий… размер таблицы (ListObject) на листе, и вдруг, совершенно неожиданно, появляется упомянутое сообщение и потом уже выходит постоянно при всяком изменении или сохранении файла, пока не перезапустишь Excel. В таблице, и на листе, где она размещена, вообще нет ни одной формулы (только исходные данные). Локализовать ошибку не удается.
Есть предположение, что это «косяк» Excel. Неужели надо воссоздать всё приложение заново, поэтапно, осторожно и с оглядкой? И тогда посмотреть. Или как?
Возможно, что никакой связи упомянутого сообщения с префисом имен «_xlfn.» нет вовсе. Кто знает, кто сталкивался с подобным?
Из-за этой ошибки приложение нельзя передать пользователям.

Источник

Excel Found a Problem with One Or More Formula References!

«One of my least favorite messages that Excel provides is: Excel found a problem with one or more formula references in this worksheet. Check that the cell references, range names, defined names, and links to other workbooks in your formulas are all correct. But I can never find bad references. It’s usually buried somewhere very deep, in a chart series formula, or a name definition, or who knows where else».

If you are experiencing the error — Excel found a problem with one or more formula references in this worksheet in the Excel workbook, it indicates that the Excel file is corrupt or partially damaged. Resolving the error is critical as it doesn’t let you save the file and may cause Excel data loss.

Here are some methods that you can follow to fix the error «Excel found a problem with one or more formula references in this worksheet».

Workable Solutions	Step-by-step Troubleshooting
Isolate the Spreadsheet Tab	Copy your tabs from the old workbook to a new one. Keep checking which tab…Full steps
Run Excel Repair Software	Download, install and launch EaseUS Data Recovery Wizard. Go to the software’s home screen and…Full steps
Check Formulas in Excel	Go to Formulas and click on the «Error Checking» button. This runs a scan…Full steps
Check the External Links	Go to the «Data» Tab. In the Connections group, click on «Edit Links». It opens…Full steps
Other Workable Solutions	Check Your Charts…Full steps Check Pivot Tables…Full steps

Solution 1. Isolate the Specific Spreadsheet Tab

The first thing you need to do is isolate the specific Excel sheet. If you have a file with lots of tabs, you may want to start by isolating the specific tab to solve the case. It’s easy to isolate the tab.

Step 1. Copy your tabs from the old workbook to a new one.

Step 2. Keep checking which tab in the new workbook throws the error message.

Step 3. Click «Save» after copying each of the tabs. The error message usually appears at the time of saving the event. Then, you can find the problematic tab.

This will help you find the faulty sheet from the workbook that you can review. In case you can’t fix the error, back up the specific sheet and remove it after the backup.

Solution 2. Run Excel Repair Software to Repair Excel Files

If Method 1 can’t solve the error — Excel found a problem with one or more formula references in this worksheet, you can turn to advanced Excel repair software. EaseUS Data Recovery Wizard is a powerful tool that can repair corrupt Word documents, Excel files, PPT files, and PDFs. Follow the steps below to recover and repair Excel files.

Step 1. Launch EaseUS Data Recovery Wizard, and then scan disk with corrupted documents. This software enables you to fix damaged Word, Excel, PPT, and PDF files in same steps.

Step 2. EaseUS data recovery and repair tool will scan for all lost and corrupted files. You can find the target files by file type or type the file name in the search box.

Step 3. EaseUS Data Recovery Wizard can repair your damaged documents automatically. After file preview, you can click «Recover» to save the repaired Word, Excel, and PDF document files to a safe location.

Solution 3. Check Formulas in Excel to Fix Excel Errors

Small and silly formula errors in excel can also lead to big consequences. To avoid such a case, it’s advised to check your formula and make it error-free.

Step 1. Go to Formulas and click on the «Error Checking» button

Step 2. This runs a scan on the sheet and displays the issues.

If no issue is found, it displays the following message:

«The error check is completed for the entire sheet.»

Then, you can try to save the Excel file again. If the error message persists, proceed to the next method.

Solution 4. Check the External Links

The next option to solve this issue is to find the external links that have errors. Follow the steps below to check out the external links containing errors:

Step 1. Go to the «Data» Tab.

Step 2. In the Connections group, click on «Edit Links». It opens the Edit Links dialog box will list all the workbooks that are being referenced.

Step 3. Check the links. If you find any faulty link, remove it and then save the sheet.

Solution 5. Check Your Charts

If you are still experiencing the fault, you can try to review the charts to solve the problem. As you know, if the error is within a chart it will pop up after you have selected the chart or when you are entering the Select Data Source dialog box.

Here is a list of area locations where you need to check for errors.

In the source data of the chart.
In each of the chart series formulas present within the Select Data Source dialog box.
Check for the errors in the horizontal axis formula which is present inside the Select Data Source dialog box.
Also, check in the chart’s Secondary Axis.
Also check in the linked Axis Labels, Data Labels, or the Chart Title.

Solution 6. Check Pivot Tables

There could be an error in your Pivot Table’s Data Source formula. To check Pivot Tables, follow these steps:

Step 1. Go to «PivotTable Tools > Analyze > Change Data Source > Change Data Source…»

Step 2. Check out whether any of your formulas are having the error or not.

Conclusion

This article has provided you with almost all the fixes to troubleshoot this error «Excel found a problem with one or more formula references in this worksheet» error. But the solutions may lead to a partial loss of information. Therefore, you’d better make a backup to external devices in advance.

Источник

Excel Found a Problem with One Or More Formula References!

Here are some methods that you can follow to fix the error «Excel found a problem with one or more formula references in this worksheet».

Workable Solutions	Step-by-step Troubleshooting
Isolate the Spreadsheet Tab	Copy your tabs from the old workbook to a new one. Keep checking which tab…Full steps
Run Excel Repair Software	Download, install and launch EaseUS Data Recovery Wizard. Go to the software’s home screen and…Full steps
Check Formulas in Excel	Go to Formulas and click on the «Error Checking» button. This runs a scan…Full steps
Check the External Links	Go to the «Data» Tab. In the Connections group, click on «Edit Links». It opens…Full steps
Other Workable Solutions	Check Your Charts…Full steps Check Pivot Tables…Full steps

Solution 1. Isolate the Specific Spreadsheet Tab

Step 1. Copy your tabs from the old workbook to a new one.

Step 2. Keep checking which tab in the new workbook throws the error message.

Step 3. Click «Save» after copying each of the tabs. The error message usually appears at the time of saving the event. Then, you can find the problematic tab.

This will help you find the faulty sheet from the workbook that you can review. In case you can’t fix the error, back up the specific sheet and remove it after the backup.

Solution 2. Run Excel Repair Software to Repair Excel Files

Step 1. Launch EaseUS Data Recovery Wizard, and then scan disk with corrupted documents. This software enables you to fix damaged Word, Excel, PPT, and PDF files in same steps.

Step 2. EaseUS data recovery and repair tool will scan for all lost and corrupted files. You can find the target files by file type or type the file name in the search box.

Solution 3. Check Formulas in Excel to Fix Excel Errors

Small and silly formula errors in excel can also lead to big consequences. To avoid such a case, it’s advised to check your formula and make it error-free.

Step 1. Go to Formulas and click on the «Error Checking» button

Step 2. This runs a scan on the sheet and displays the issues.

If no issue is found, it displays the following message:

«The error check is completed for the entire sheet.»

Then, you can try to save the Excel file again. If the error message persists, proceed to the next method.

Solution 4. Check the External Links

The next option to solve this issue is to find the external links that have errors. Follow the steps below to check out the external links containing errors:

Step 1. Go to the «Data» Tab.

Step 2. In the Connections group, click on «Edit Links». It opens the Edit Links dialog box will list all the workbooks that are being referenced.

Step 3. Check the links. If you find any faulty link, remove it and then save the sheet.

Solution 5. Check Your Charts

Here is a list of area locations where you need to check for errors.

In the source data of the chart.
In each of the chart series formulas present within the Select Data Source dialog box.
Check for the errors in the horizontal axis formula which is present inside the Select Data Source dialog box.
Also, check in the chart’s Secondary Axis.
Also check in the linked Axis Labels, Data Labels, or the Chart Title.

Solution 6. Check Pivot Tables

There could be an error in your Pivot Table’s Data Source formula. To check Pivot Tables, follow these steps:

Step 1. Go to «PivotTable Tools > Analyze > Change Data Source > Change Data Source…»

Step 2. Check out whether any of your formulas are having the error or not.

Conclusion

Источник

Формула на этом листе содержит одну или несколько ошибок недопустимых ссылок

Я использую один и тот же код много раз с небольшими изменениями номеров строк для создания диаграмм одного типа. Но следующее окно сообщения появляется на листе, где построены диаграммы.

«Формула на этом листе содержит одну или несколько недопустимых ссылок. Убедитесь, что ваша формула содержит допустимый путь, книгу, имя диапазона и ссылку на ячейку».

Как избавиться от этого окна сообщения? Я пробовал использовать

Но это не работает.

3 ответа

Вам нужно будет найти формулу и либо изменить ссылку, либо разорвать ее, в девяти случаях из десяти у вас будет ссылка на ячейку, ведущую к листу или рабочей книге, которые больше не существуют (или не открыты. ), это будет происходить чаще, если вы используете .delete в своем скрипте

К сожалению, Excel не позволяет легко найти эту ссылку, вы можете посмотреть на вкладке данных и увидеть существующие соединения, а затем разорвать их. Но предыдущий опыт не давал такой возможности все время.

Вы также можете попробовать этот макрос от Аллена Уайатта, который проверит ваши листы и создаст новый лист. со списком возможных ошибок ошибок формулы .

Принятое решение не сработало для меня, но я мог сразу решить его с помощью этого бесплатного надстройки Excel.

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

Я упростил формулы всех названных диапазонов, например:

Я поместил последнюю часть «8-CountBlank(Other named range)» для вычисления в Рабочий лист и заменил ее в формуле для диапазона с результатом. Если диаграмма была полностью пустой, всплывающие ошибки не отображались. Я надеюсь когда-нибудь помочь кому-нибудь с той же проблемой.

Формула на этом листе содержит одну или несколько ошибок недопустимых ссылок

Я использую один и тот же код много раз с небольшими изменениями в номерах строк для создания диаграмм того же типа. Но на листе, где отображаются графики, появляется всплывающее окно msg.

“Формула на этом листе содержит одну или несколько недопустимых ссылок. Убедитесь, что ваша формула содержит допустимый путь, книгу, имя диапазона и ссылку на ячейку”.

Как избавиться от этого окна сообщений? Я попытался использовать

но это не сработает.

Вам нужно будет найти формулу и либо изменить ссылку, либо сломать ее, девять раз из десяти, у вас будет ссылка на ячейку на лист или рабочую книгу, которая больше не существует (или не открыта), это произойдет больше, если вы используете .delete в своем скрипте

К сожалению, Excel не позволяет легко найти эту ссылку, вы можете просмотреть вкладку данных и увидеть существующие соединения, а затем разбить их. Но предыдущий опыт не имел этой работы все время.

Вы также можете попробовать этот макрос от Allen Wyatt, который проверит ваши листы и создаст новый лист со списком возможных ошибок формулы ошибок.

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

Я упростил все формулы названных диапазонов, например:

Я поместил последнюю часть «8-CountBlank(Other named range)» для вычисления в листе и заменил ее в формуле для диапазона с результатом. Если диаграмма не была полностью пустой, отображались больше ошибок всплывающих окон. Я надеюсь, что когда-нибудь кто-нибудь помог бы с той же проблемой.

Формула на этом листе содержит одну или несколько ошибок недопустимых ссылок

Как найти формулу, содержащую недопустимую ссылку, в Excel: Использование Microsoft Excel

«Формула на этом листе содержит одну или несколько недопустимых ссылок. Убедитесь, что формула содержит допустимый путь, книгу, имя диапазона и ссылку на ячейку».

Как избавиться от этого окна сообщения? Я пробовал использовать

но это не работает.

Быстрый способ найти все недопустимые ссылки: F5 -> Special, выберите «Формулы», затем отметьте только «Ошибки». Если он выбирает несколько ячеек, меняет их фон на красный, а затем исправляет их все по одной

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

Спасибо, @Jake. Я собираюсь попробовать это. Между тем, не могли бы вы помочь мне с другим вопросом bit.ly/1J9ndmu (ищу решение второй части — части легенды)

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

Я упростил формулы всех названных диапазонов, например:

Я поставил последнюю часть ‘8-CountBlank(Other named range)’ для вычисления на рабочем листе и замены этого в формуле для диапазона на результат. Если диаграмма была полностью пустой, всплывающие ошибки не отображались. Я надеюсь когда-нибудь помочь кому-нибудь с той же проблемой.

ссылки в диаграммах также могут создавать эту ошибку, вы просматривали данные и видели, где вы видите #REF

Принятое решение не сработало для меня, но я мог сразу решить его с помощью этого бесплатного надстройки Excel.

Источник

Excel

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

Вы пытаетесь сохранить файл Excel, над которым так усердно работали, но появляется сообщение об ошибке: Excel обнаружил проблему с одной или несколькими ссылками на формулы.

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

К счастью, есть некоторые исправления, которые вы можете попробовать, чтобы найти корень проблемы и убедиться, что ошибка «Excel обнаружила проблему с одной или несколькими ссылками на формулы» больше не появляется.

Используйте инструмент проверки ошибок

Один из самых быстрых способов найти ошибки в электронной таблице Excel — использовать инструмент проверки ошибок. Если инструмент обнаружит какие-либо ошибки, вы можете исправить их и попробовать сохранить файл еще раз. Однако инструмент может не найти все типы ошибок.

Если средство проверки ошибок не находит ошибок или их исправление не решает проблему, попробуйте одно из других исправлений, указанных ниже.

Чтобы использовать инструмент проверки ошибок в Excel:

Выберите вкладку Формулы.
Щелкните инструмент проверки ошибок.
При обнаружении ошибки в инструменте появится информация о причине ошибки.
Либо исправьте ошибку, либо удалите формулу, вызывающую проблему, если она не нужна.
В инструменте проверки ошибок нажмите «Далее», чтобы просмотреть следующую ошибку и повторить процесс.
Когда больше ошибок не будет найдено, вы увидите всплывающее окно, информирующее вас об этом факте.
Повторите процесс для каждого листа документа.
Попробуйте сохранить документ еще раз, чтобы проверить, исправлена ли ошибка ссылки на формулу.

Найдите проблемный лист

Если средство проверки ошибок не находит причину вашей проблемы, вам нужно начать поиск самостоятельно.

Например, если у вас есть документ с несколькими листами, вам нужно сузить проблему до одного листа (или нескольких листов), вызывающего проблему.

Чтобы найти лист, вызывающий ошибку ссылки на формулу:

Откройте первый лист в электронной таблице.
Нажмите Ctrl+A в Windows или Cmd+A в Mac, чтобы выбрать весь лист. Кроме того, вы можете нажать кнопку в самом верхнем левом углу таблицы.
Щелкните правой кнопкой мыши и выберите Копировать.
Либо используйте сочетание клавиш Ctrl+C в Windows или Cmd+C в Mac.
Далее откройте меню «Файл».
Выберите Пустая книга.
Щелкните правой кнопкой мыши ячейку A1 и выберите значок «Вставить».
Либо используйте сочетание клавиш Ctrl+V в Windows или Cmd+V в Mac.
Попробуйте сохранить новый файл. Если вы можете сохранить файл без ошибок, то этот конкретный лист не содержит источника вашей ошибки.
Повторите эти действия с каждым листом документа, пока не найдете лист или листы, вызывающие проблемы.

Поиск ошибок

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

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

Чтобы найти ошибки на листе Excel:

Откройте лист, который, как вы обнаружили, содержит потенциальные ошибки.
Нажмите Ctrl+F в Windows или Cmd+F в Mac, чтобы открыть инструмент поиска.
Введите # в поле «Найти» и убедитесь, что для параметра «Искать в» установлено значение «Значения».
Нажмите «Найти все», чтобы найти все экземпляры ячейки, содержащие хэштег.
Если какие-либо экземпляры будут найдены, они появятся в инструменте поиска. Вы можете нажать на каждую из них, чтобы перейти к этой ячейке и внести необходимые изменения, или просто удалить формулы, если они вам не нужны.
Повторите процесс для всех других листов, которые, по вашему мнению, могут содержать ошибки.
Попробуйте сохранить документ еще раз, чтобы узнать, устранена ли проблема.

Проверьте свои графики

Ошибка «Excel обнаружила проблему с одной или несколькими ссылками на формулы» также часто может быть вызвана недопустимыми ссылками на ваши диаграммы. Исправление этих ссылок может решить проблему.

Чтобы проверить исходные данные для ваших диаграмм Excel:

Откройте лист, содержащий вашу диаграмму.
Щелкните правой кнопкой мыши диаграмму и выберите «Выбрать данные».
Убедитесь, что диапазон данных диаграммы указан правильно и относится к действительному диапазону ячеек, содержащих данные.
Если это не так, измените диапазон, чтобы он ссылался на правильные ячейки, или удалите диаграмму, если она вам больше не нужна.
Попробуйте сохранить документ Excel еще раз, чтобы проверить, возникает ли ошибка.

Проверьте внешние ссылки

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

К счастью, в Excel есть инструмент, который может помочь вам проверить статус любых внешних ссылок.

Чтобы проверить действительность внешних ссылок в Excel:

Щелкните меню Данные.
В разделе «Запросы и подключения» на ленте щелкните инструмент «Редактировать ссылки».
Вы увидите список внешних ссылок в вашем документе со статусом Неизвестно.
Нажмите кнопку Проверить статус.
Статус ваших ссылок будет обновлен.
Если одна из ваших ссылок показывает ошибку, исходный файл не может быть найден. Вы можете обновить местоположение исходного файла, нажав «Изменить источник» и выбрав файл, на который хотите сослаться.
Если вам больше не нужно ссылаться на другой документ, вы можете удалить ячейку, содержащую внешнюю ссылку.
Если многие ячейки относятся к документу, который больше не доступен, вы можете полностью удалить ссылку из документа, щелкнув эту ссылку и выбрав «Разорвать ссылку». Это следует делать с осторожностью, так как вы потеряете все внешние ссылки на эту ссылку.
Вам нужно будет подтвердить свое решение, нажав «Разорвать ссылки».
После того как вы исправите или удалите мошеннические ссылки, попробуйте снова сохранить документ.

Проверьте свои сводные таблицы

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

Чтобы проверить свои сводные таблицы на наличие ошибок в Excel:

Откройте лист, содержащий вашу сводную таблицу.
Выберите сводную таблицу.
На ленточной панели щелкните появившееся меню «Анализ сводной таблицы».
На ленте щелкните Изменить источник данных.
Проверьте таблицу/диапазон, чтобы убедиться, что он относится к допустимому диапазону ячеек.
Если это не так, отредактируйте значения таблицы/диапазона или, если вам это не нужно, удалите сводную таблицу.
Попробуйте сохранить документ еще раз.

Если вы видите всплывающее сообщение об ошибке «Excel обнаружил проблему с одной или несколькими ссылками на формулы», возможно, одно из приведенных выше исправлений решит проблему. Если вы найдете какие-либо другие способы предотвратить появление ошибки, сообщите нам об этом в комментариях ниже.

Изучение того, как исправить некоторые из наиболее распространенных ошибок Excel, поможет вам тратить больше времени на работу и меньше времени на то, чтобы рвать на себе волосы. Например, полезно знать, как найти циклическую ссылку в Excel.

Если Excel продолжает давать сбой, есть несколько полезных исправлений, которые вы можете попробовать. Если вы хотите игнорировать любые проблемы на листе, вы можете вместо этого игнорировать все ошибки в Excel.

Источник

You’re going to need to find the formula and either alter the link or break it, nine times out of ten, you’ll have a cell reference going to a sheet or a work book that no longer exists (or isn’t open), this will occur more if you’re using .delete in your script

Unfortunately, Excel does not make it easy to find this link, you can look in the data tab and see existing connections, then break them. But previous experience has not had this work all the time.

You can also try this macro from Allen Wyatt, which will check your sheets and create a new sheet with a list of potential errors formula errors.

Sub CheckReferences()
' Check for possible missing or erroneous links in
' formulas and list possible errors in a summary sheet

  Dim iSh As Integer
  Dim sShName As String
  Dim sht As Worksheet
  Dim c, sChar As String
  Dim rng As Range
  Dim i As Integer, j As Integer
  Dim wks As Worksheet
  Dim sChr As String, addr As String
  Dim sFormula As String, scVal As String
  Dim lNewRow As Long
  Dim vHeaders

  vHeaders = Array("Sheet Name", "Cell", "Cell Value", "Formula")
  'check if 'Summary' worksheet is in workbook
  'and if so, delete it
  With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
    .Calculation = xlCalculationManual
  End With

  For i = 1 To Worksheets.Count
    If Worksheets(i).Name = "Summary" Then
      Worksheets(i).Delete
    End If
  Next i

  iSh = Worksheets.Count

  'create a new summary sheet
    Sheets.Add After:=Sheets(iSh)
    Sheets(Sheets.Count).Name = "Summary"
  With Sheets("Summary")
    Range("A1:D1") = vHeaders
  End With
  lNewRow = 2

  ' this will not work if the sheet is protected,
  ' assume that sheet should not be changed; so ignore it
  On Error Resume Next

  For i = 1 To iSh
    sShName = Worksheets(i).Name
    Application.Goto Sheets(sShName).Cells(1, 1)
    Set rng = Cells.SpecialCells(xlCellTypeFormulas, 23)

    For Each c In rng
      addr = c.Address
      sFormula = c.Formula
      scVal = c.Text

      For j = 1 To Len(c.Formula)
        sChr = Mid(c.Formula, j, 1)

        If sChr = "[" Or sChr = "!" Or _
          IsError(c) Then
          'write values to summary sheet
          With Sheets("Summary")
            .Cells(lNewRow, 1) = sShName
            .Cells(lNewRow, 2) = addr
            .Cells(lNewRow, 3) = scVal
            .Cells(lNewRow, 4) = "'" & sFormula
          End With
          lNewRow = lNewRow + 1
          Exit For
        End If
      Next j
    Next c
  Next i

' housekeeping
  With Application
    .ScreenUpdating = True
    .DisplayAlerts = True
    .Calculation = xlCalculationAutomatic
  End With

' tidy up
  Sheets("Summary").Select
  Columns("A:D").EntireColumn.AutoFit
  Range("A1:D1").Font.Bold = True
  Range("A2").Select
End Sub

Источник

Unfortunately, Excel does not make it easy to find this link, you can look in the data tab and see existing connections, then break them. But previous experience has not had this work all the time.

You can also try this macro from Allen Wyatt, which will check your sheets and create a new sheet with a list of potential errors formula errors.

Sub CheckReferences()
' Check for possible missing or erroneous links in
' formulas and list possible errors in a summary sheet

  Dim iSh As Integer
  Dim sShName As String
  Dim sht As Worksheet
  Dim c, sChar As String
  Dim rng As Range
  Dim i As Integer, j As Integer
  Dim wks As Worksheet
  Dim sChr As String, addr As String
  Dim sFormula As String, scVal As String
  Dim lNewRow As Long
  Dim vHeaders

  vHeaders = Array("Sheet Name", "Cell", "Cell Value", "Formula")
  'check if 'Summary' worksheet is in workbook
  'and if so, delete it
  With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
    .Calculation = xlCalculationManual
  End With

  For i = 1 To Worksheets.Count
    If Worksheets(i).Name = "Summary" Then
      Worksheets(i).Delete
    End If
  Next i

  iSh = Worksheets.Count

  'create a new summary sheet
    Sheets.Add After:=Sheets(iSh)
    Sheets(Sheets.Count).Name = "Summary"
  With Sheets("Summary")
    Range("A1:D1") = vHeaders
  End With
  lNewRow = 2

  ' this will not work if the sheet is protected,
  ' assume that sheet should not be changed; so ignore it
  On Error Resume Next

  For i = 1 To iSh
    sShName = Worksheets(i).Name
    Application.Goto Sheets(sShName).Cells(1, 1)
    Set rng = Cells.SpecialCells(xlCellTypeFormulas, 23)

    For Each c In rng
      addr = c.Address
      sFormula = c.Formula
      scVal = c.Text

      For j = 1 To Len(c.Formula)
        sChr = Mid(c.Formula, j, 1)

        If sChr = "[" Or sChr = "!" Or _
          IsError(c) Then
          'write values to summary sheet
          With Sheets("Summary")
            .Cells(lNewRow, 1) = sShName
            .Cells(lNewRow, 2) = addr
            .Cells(lNewRow, 3) = scVal
            .Cells(lNewRow, 4) = "'" & sFormula
          End With
          lNewRow = lNewRow + 1
          Exit For
        End If
      Next j
    Next c
  Next i

' housekeeping
  With Application
    .ScreenUpdating = True
    .DisplayAlerts = True
    .Calculation = xlCalculationAutomatic
  End With

' tidy up
  Sheets("Summary").Select
  Columns("A:D").EntireColumn.AutoFit
  Range("A1:D1").Font.Bold = True
  Range("A2").Select
End Sub

Источник

Связывание с другими книгами — очень распространенная задача в Excel, но иногда вы можете найти книгу, которая ссылок не может найти, хотя Excel сообщает о том, что она существует. Нет способа автоматического поиска всех внешних ссылок, используемых в книге, однако существует несколько способов, используемых вручную. Вам нужно искать формулы, определенные имена, объекты (например, текстовые поля или фигуры), названия диаграмм и ряды данных диаграммы.

Имя любой Excel книги, с помощью ссылки на которую вы ссылались, будет связана с расширением XL* (например, .xls, .xlsx, XLSM), поэтому рекомендуемый способ — найти все ссылки на частичное расширение XL. Если вы ссылались на другой источник, необходимо определить оптимальный поисковый запрос.

Поиск ссылок, используемых в формулах

Нажмите CTRL+F, чтобы запустить диалоговое окно Найти и заменить.

Нажмите кнопку Параметры.

В поле Найти введите.xl.

В поле В пределах выберите книга.

В поле Искать в выберите формулы.

Нажмите кнопку Найти все.

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

Чтобы выбрать ячейку с внешней ссылкой, щелкните ссылку на эту строку в списке.

Совет: Щелкните любой за колонок, чтобы отсортировать столбец и сгруппировать все внешние ссылки.

На вкладке Формулы в группе Определенные имена выберите пункт Диспетчер имен.

Проверьте каждую запись в списке и проверьте, нет ли в столбце Ссылка внешних ссылок. Внешние ссылки содержат ссылку на другую книгу, например [Budget.xlsx].

Щелкните любой за колонок, чтобы отсортировать столбец и сгруппировать все внешние ссылки.

Если вы хотите удалить сразу несколько элементов, можно сгруппнуть несколько элементов, нажав клавишу SHIFT или CTRL и щелкнув левой кнопкой мыши.

Нажмите клавиши CTRL+G, нажмите клавиши CTRL+G, чтобы перейти в диалоговое окно Перейти, а затем выберите специальные > объекты > ОК. При этом будут выбраны все объекты на активном сайте.

Нажимая клавишу TAB, переходить между выбранными объектами, а затем искать в строка формул ссылку на другую книгу, например [Budget.xlsx].

Щелкните название диаграммы, которую вы хотите проверить.

В строка формул наймем ссылку на другую книгу, например [Budget.xls].

Выберите диаграмму, которую нужно проверить.

На вкладке Макет в группе Текущий выделение щелкните стрелку рядом с полем Элементы диаграммы и выберите ряд данных, которые нужно проверить.

На строка формул , наймем ссылку на другую книгу, например [Budget.xls] в функции РЯД.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Как исправить ошибку #ССЫЛКА! в Excel

При работе в Excel можно столкнуться с ошибкой #ССЫЛКА!. Эта ошибка возникает тогда, когда функция ссылается на ячейку, которой не существует. В этой статье мы разберем основные способы, как исправить эту ситуацию.

Вариант 1. Исправление формул

Представим такую ситуацию. У нас есть файл с базой данных, где хранятся данные по продажам за январь — февраль (лист в файле назван «БД янв — фев») и файл со сводными данными, который суммирует выручку по месяцам. Мы открыли файл с базой данных, внесли в него данные по продажам за март и внимание(!) переименовали лист в «БД янв — март», после этого открываем файл со сводом и видим там вместо данных по выручке за январь, февраль, что мы делали раньше, ошибку #ССЫЛКА!. Это произошло из-за того, что функция ссылается на данные в листе «БД янв — фев», которого нет, так как мы его переименовали.

Что бы исправить эту ситуацию, закрываем файл со сводом, не сохраняя изменения (!), если вы сохраните изменения — то формулы будут навсегда испорчены и вам придется писать их заново. Далее в файле с базой данных переименовываете лист так, как он назывался ранее, т.е. «БД янв — фев» и снова открываете файл со сводом. Ошибка пропала, функции снова работают. Теперь при открытых двух файлах, вы можете переименовать лист в базе данных и ошибка не появится, так как Excel изменит ссылки в функциях (он умеет это делать только в открытых книгах).

Вариант 2. Функция ЕСЛИОШИБКА

При помощи функции ЕСЛИОШИБКА можно обработать ошибки, которые возникают при написании формул, в том числе ошибку #ССЫЛКА!.

Синтаксис функции ЕСЛИОШИБКА следующий: первым аргументом идет функция, которая может вызвать ошибку, вторым аргументом функционал, который будет вызван, если ошибка произошла.

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

Исправлено: ссылки на источники данных недопустимы в Excel —

Некоторые пользователи получают «Ссылка на источник данных недействительна”При попытке создать сводную таблицу из диапазона в Excel. Сообщается, что эта конкретная ошибка возникает в нескольких версиях Excel и Windows, поэтому проблема не связана с конкретной версией ОС или Excel.

Ссылка на источник не верна

Что вызывает ошибку «Недопустимая ссылка на источник данных» в Excel?

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

Имя файла Excel содержит квадратные скобки — Есть набор запрещенных символов, которые не поддерживаются в сводных таблицах. Одна из распространенных причин, по которой вы можете увидеть эту конкретную ошибку, заключается в том, что имя файла Excel содержит ‘[‘ или ‘]’. В этом случае проблему можно решить, отредактировав имя файла и убрав квадратные скобки.
Файл Excel не сохраняется на локальном диске — Возможно, вы видите это конкретное сообщение об ошибке, если пытаетесь создать сводную таблицу из файла Excel, открытого непосредственно с веб-сайта или из вложения электронной почты. В этом случае файл будет открыт из временной папки, которая не имеет тех же разрешений, что и обычная папка. Если этот сценарий применим, вы можете решить эту проблему, предварительно сохранив файл .xlsx на локальном диске.
Данные сводной таблицы относятся к несуществующему диапазону — Диапазон, который не определен должным образом, также может быть причиной этой конкретной ошибки. Если вы пытаетесь создать сводную таблицу с диапазоном, который не был определен, вы получите то же сообщение об ошибке. В этом случае вы можете решить проблему, определив ярость, прежде чем пытаться создать сводную таблицу.
Источник данных ссылается на именованный диапазон, который содержит недопустимые ссылки. — Другая возможная причина, по которой возникает эта ошибка, — это когда диапазон определен, но он ссылается на недопустимые значения. В этом случае вы можете использовать Диспетчер имен для определения указанных значений и внесения необходимых изменений для решения проблемы.

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

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

Способ 1: удаление скобок из имени файла

Одна из самых популярных причин, которая в конечном итоге вызывает Ссылка на источник данных недействительна ошибка — неправильное имя файла Excel. Если отчеты создаются системой автоматизации отчетов и имя содержит запрещенные символы, такие как квадратные скобки ‘[]‘, Вы получите это сообщение об ошибке всякий раз, когда вы пытаетесь создать сводную таблицу.

Если этот сценарий применим к вашей текущей ситуации, вы сможете решить проблему, изменив имя файла .xlsx, удалив запрещенные символы. Вот краткое руководство по этому:

Закройте окно Excel, которое в данный момент использует файл. Если файл используется, вы не сможете его переименовать.
Используйте проводник, чтобы перейти к местоположению файла Excel. Как только вы попадете туда, щелкните по нему правой кнопкой мыши и выберите Переименовать.
Далее, удалите скобки из имени файла, так как таблица Pivots не настроена для их поддержки.
Снятие скобок с имени файла
Попытайтесь заново создать сводную таблицу и посмотрите, не возникла ли еще ошибка.

Если вы все еще сталкиваетесь с Ссылка на источник данных недействительна Ошибка или этот метод не применим к вашему конкретному сценарию, перейдите к следующему способу ниже.

Способ 2: сохранение файла на локальном диске

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

Если этот сценарий применим к вашему текущему сценарию, вы сможете решить эту проблему, предварительно сохранив файл Excel на локальном диске. Итак, прежде чем пытаться создать сводную таблицу, перейдите к Файл> Сохранить как и сохраните файл в физическом месте (на локальном диске).

Сохранение файла Excel на локальном диске

Однажды превосходить файл был сохранен локально, воссоздайте шаги, которые ранее вызывали Ссылка на источник данных недействительна ошибки и посмотрите, можете ли вы создать таблицу Pivots, не обнаружив ошибки.

Если этот метод не подходит для вашей текущей ситуации, перейдите к следующему способу ниже.

Метод 3: Обеспечение того, что диапазон существует и он определен

Другая причина, по которой вы можете столкнуться с ошибкой «Ссылка на источник данных недействительна». При попытке вставить сводную таблицу — это несуществующий / не определенный диапазон.

Для наглядности предположим, что вы пытаетесь создать сводную таблицу. Естественно, вы идете в Вставить сводную таблицу, вы выбираете переключатель, связанный с Выберите стол или диапазон и установить Таблица / Диапазон в ‘тестовое задание’. Теперь, если вы основали значения на «тестовом» диапазоне, а он не существует, вы увидите ошибку «Ссылка на источник данных недействительна». Как только вы нажмете Хорошо.

Видя ошибку из-за несуществующего диапазона

Если этот сценарий применим, вы сможете обойти проблему, определив диапазон, прежде чем пытаться создать сводную таблицу. Вот краткое руководство о том, как это сделать.

Нажмите на Формулы вкладка из панели ленты, затем нажмите на Имя менеджера из списка.
Внутри Имя менеджера окно, нажмите на новый и назовите диапазон, который вы собираетесь создать. Затем используйте Относится к поле, чтобы установить ячейки, которые вы хотите использовать для диапазона. Вы можете либо напечатать его самостоятельно, либо использовать встроенный селектор.
Теперь, когда диапазон определен, вы можете успешно создать сводную таблицу, не встречая того же сообщения об ошибке.

Если этот сценарий не применим или вы все еще сталкиваетесь с Недопустимая ссылка на источник данных даже после выполнения действий, описанных выше, перейдите к следующему способу ниже.

Метод 4: Обеспечение того, чтобы ссылка для именованного диапазона была действительной

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

Чтобы исправить это, перейдите на Формулы> Имя Менеджер и посмотрите, относится ли диапазон к ячейкам, которые вы хотите проанализировать через сводную таблицу. Если вы видите какие-либо несоответствия, используйте Ссылаться на: поле, чтобы сделать переключение на правильное значение.

Обеспечение того, что ссылки действительны

После внесения изменений попробуйте снова создать сводную таблицу и посмотрите, не появляется ли по-прежнему такое же сообщение об ошибке.

Как найти недопустимые ссылки в excel

Поиск ссылок, используемых в формулах

Нажмите CTRL+F, чтобы запустить диалоговое окно Найти и заменить.

Нажмите кнопку Параметры.

В поле Найти введите.xl.

В поле В пределах выберите книга.

В поле Искать в выберите формулы.

Нажмите кнопку Найти все.

Чтобы выбрать ячейку с внешней ссылкой, щелкните ссылку на эту строку в списке.

Совет: Щелкните любой за колонок, чтобы отсортировать столбец и сгруппировать все внешние ссылки.

На вкладке Формулы в группе Определенные имена выберите пункт Диспетчер имен.

Щелкните любой за колонок, чтобы отсортировать столбец и сгруппировать все внешние ссылки.

Щелкните название диаграммы, которую вы хотите проверить.

В строка формул наймем ссылку на другую книгу, например [Budget.xls].

Выберите диаграмму, которую нужно проверить.

На строка формул , наймем ссылку на другую книгу, например [Budget.xls] в функции РЯД.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Исправлено: ссылки на источники данных недопустимы в Excel —

Ссылка на источник не верна

Что вызывает ошибку «Недопустимая ссылка на источник данных» в Excel?

Имя файла Excel содержит квадратные скобки — Есть набор запрещенных символов, которые не поддерживаются в сводных таблицах. Одна из распространенных причин, по которой вы можете увидеть эту конкретную ошибку, заключается в том, что имя файла Excel содержит ‘[‘ или ‘]’. В этом случае проблему можно решить, отредактировав имя файла и убрав квадратные скобки.
Файл Excel не сохраняется на локальном диске — Возможно, вы видите это конкретное сообщение об ошибке, если пытаетесь создать сводную таблицу из файла Excel, открытого непосредственно с веб-сайта или из вложения электронной почты. В этом случае файл будет открыт из временной папки, которая не имеет тех же разрешений, что и обычная папка. Если этот сценарий применим, вы можете решить эту проблему, предварительно сохранив файл .xlsx на локальном диске.
Данные сводной таблицы относятся к несуществующему диапазону — Диапазон, который не определен должным образом, также может быть причиной этой конкретной ошибки. Если вы пытаетесь создать сводную таблицу с диапазоном, который не был определен, вы получите то же сообщение об ошибке. В этом случае вы можете решить проблему, определив ярость, прежде чем пытаться создать сводную таблицу.
Источник данных ссылается на именованный диапазон, который содержит недопустимые ссылки. — Другая возможная причина, по которой возникает эта ошибка, — это когда диапазон определен, но он ссылается на недопустимые значения. В этом случае вы можете использовать Диспетчер имен для определения указанных значений и внесения необходимых изменений для решения проблемы.

Способ 1: удаление скобок из имени файла

Закройте окно Excel, которое в данный момент использует файл. Если файл используется, вы не сможете его переименовать.
Используйте проводник, чтобы перейти к местоположению файла Excel. Как только вы попадете туда, щелкните по нему правой кнопкой мыши и выберите Переименовать.
Далее, удалите скобки из имени файла, так как таблица Pivots не настроена для их поддержки.
Снятие скобок с имени файла
Попытайтесь заново создать сводную таблицу и посмотрите, не возникла ли еще ошибка.

Способ 2: сохранение файла на локальном диске

Сохранение файла Excel на локальном диске

Если этот метод не подходит для вашей текущей ситуации, перейдите к следующему способу ниже.

Метод 3: Обеспечение того, что диапазон существует и он определен

Видя ошибку из-за несуществующего диапазона

Нажмите на Формулы вкладка из панели ленты, затем нажмите на Имя менеджера из списка.
Внутри Имя менеджера окно, нажмите на новый и назовите диапазон, который вы собираетесь создать. Затем используйте Относится к поле, чтобы установить ячейки, которые вы хотите использовать для диапазона. Вы можете либо напечатать его самостоятельно, либо использовать встроенный селектор.
Теперь, когда диапазон определен, вы можете успешно создать сводную таблицу, не встречая того же сообщения об ошибке.