Общая формула
=INDIRECT(sheet&"!"&CELL("address",A1))
Резюме
Чтобы увеличить ссылку, созданную в виде текста внутри функции ДВССЫЛ, вы можете использовать функцию ЯЧЕЙКА. В показанном примере формула в D5:
=INDIRECT($B$5&"!"&CELL("address",A1))
Который увеличивается при копировании формулы.
Объяснение
Рассмотрим простую динамическую ссылку на Sheet2 с использованием КОСВЕННОСТИ в формуле, подобной этой:
=INDIRECT($B$5&"!"&"A1"))
Если мы изменим имя листа в B5 на другое (допустимое) имя, INDIRECT вернет ссылку на A1 в новом листе.
Однако, если мы скопируем эту формулу вниз по столбцу, ссылка на A1 не изменится, потому что «A1» жестко закодирован как текст.
Чтобы решить эту проблему, мы используем функцию ЯЧЕЙКА для генерации текстовой ссылки из обычной ссылки на ячейку:
CELL("address",A1)
С «адресом» в качестве первого аргумента и A1 в качестве второго аргумента функция ЯЧЕЙКА возвращает строку типа «$ A $ 1». Поскольку A1 - это обычная ссылка на ячейку, она обычно увеличивается по мере того, как формула копируется в столбец. Результат в D5: D9 представляет собой ряд формул, подобных этой:
=INDIRECT("Sheet2!$A$1") =INDIRECT("Sheet2!$A$2") =INDIRECT("Sheet2!$A$3") =INDIRECT("Sheet2!$A$4") =INDIRECT("Sheet2!$A$5")
В каждом случае INDIRECT преобразует каждую текстовую строку в ссылку, а Excel возвращает значение в заданной ячейке Sheet2.
Примечание: как INDIRECT, так и CELL являются непостоянными функциями и пересчитываются при каждом изменении рабочего листа. Это может вызвать проблемы с производительностью на более сложных листах.
