Почему я получаю #REF! ошибка после сортировки?

Question

В своей книге я использую эту формулу:

=INDEX('G:\Manufacturing\WH\EXPORTS\Scanning_Reports [ScanningReport.xls]Sheet1'!$D$1:$D$65536;AB3)

Это работает нормально, но когда я пытаюсь отсортировать данные, я #ref! ошибка, как я могу избежать этого ??

Это формула в AB3

=MATCH(K3;'G:\Manufacturing\WH\EXPORTS\Scanning_Reports\[ScanningReport.xls]Sheet1'!$I$1:$I$65536;0)

Когда я сортирую или удаляю что-то, этот получает ошибку #na, не знаю почему !! Ошибка исчезнет простым перетаскиванием кода.

Я считаю, что это проблема, но я не знаю, как это исправить

Excel displays this error to prompt you to correct the formulas yourself so that you don't get unexpected results

Excel автоматически корректирует формулы, но выдает #ref, чтобы уведомить меня об изменениях. Изменения в порядке.

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

Я использую Excel 2010

Answer 1

Попробуйте использовать ссылки на весь столбец вместо адреса в диапазоне от первой до последней строки.

=MATCH(K3;'G:\Manufacturing\WH\EXPORTS\Scanning_Reports\[ScanningReport.xls]Sheet1'!$I:$I;0)

То же самое для функции индекса. Это будет более надежным при вставке и удалении строк.

Вы действительно используете 65 тысяч строк данных? Вы можете подумать о динамических диапазонах, которые ограничивают диапазон поиска только используемыми строками.

Answer 2

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

На этой странице есть ссылка на надстройку, которую вы можете установить в Excel 2010, которая даст вам функцию INDEX.EXT, которая может работать с закрытыми книгами. Обратите внимание на то, что автор Ашиш Матур говорит об эффективности использования этого дополнения.