Определение имени листа в EXCEL для использования в функции ДВССЫЛ()

При изменении имени листа, все ссылки в формулах автоматически обновятся и будут продолжать работать. Исключение составляет функция ДВССЫЛ() , в которой имя листа может фигурировать в текстовой форме ДВССЫЛ("Лист1!A1") . В статье показано как использовать функцию ЯЧЕЙКА() , чтобы сохранить работоспособность формулы с функцией ДВССЫЛ() .

Имя листа можно определить с помощью функции ЯЧЕЙКА() , об этом читайте в статье Определяем имя листа .

Предположим, что в ячейке B 4 на Листе1 имеется формула =ДВССЫЛ("лист2!A1") . Если Лист2 переименовать в Лист3 , то вышеуказанная формула работать не будет. Чтобы работоспособность формулы сохранилась - определим имя листа с помощью функции ЯЧЕЙКА() (см. файл примера ).

• Запишем на Листе1 в ячейке B1 формулу =ЯЧЕЙКА("адрес";лист2!A1) Формула вернет результат [_Определяем_имя_листа.xlsx]Лист2!$A$1 , т.е. полный адрес ячейки с указанием названия книги и имени листа (ссылка должна быть на столбец с названием из одной буквы, например, ссылка лист2!AВ1 не годится);
• Предполагая, что название книги не содержит квадратных скобок [  ], запишем формулу для изъятия из полученного результата имени листа: =ПСТР(B1;ПОИСК("]";B1)+1;ДЛСТР(B1)-ПОИСК("]";B1)-5) ;
• Если в качестве названия листа использовано число, то функция ЯЧЕЙКА() возвращает название книги и листа в апострофах ('), например, '[_Определяем_имя_листа.xlsx]123456'!$A$1 , что может привести к ошибке при определении имени листа;
• Записав в ячейке В2 формулу =ЕСЛИОШИБКА(ПОИСК("'";B1);0) , получим, что если название листа – число, то результат =1, если текст, то 0;
• Слегка модифицируем формулу в ячейке B3 для определения названия листа: =ПСТР(B1;ПОИСК("]";B1)+1;ДЛСТР(B1)-ПОИСК("]";B1)-5-B2) ;

Теперь, записав вместо формулы =ДВССЫЛ("лист2!A1") формулу =ДВССЫЛ(B3&"!A1") , мы решим задачу: изменение имени Листа2 не повлияет на работоспособность формулы.

Внимание! Иногда, когда открыто несколько книг, функция ЯЧЕЙКА() может работать некорректно. Для восстановления работоспособности формулы нужно нажать клавишу F9 ( Формулы/ Вычисления/ Пересчет ).

ПРИМЕЧАНИЕ : С помощью обычных формул (не VBA) невозможно определить имя активного листа и адрес активной ячейки .