У меня есть следующая формула, возвращающая #REF! в течение долгого времени, независимо от того, как я его перепроектирую. Значение на C1 – это имя вкладки, на которой имеются данные в ссылочных ячейках:
Aug 2012
Эта часть формулы возвращает правильное числовое значение:
=INDIRECT("'" & C1 & "'" & "!A502'")
но эта формула дает ошибку:
=SUM(INDIRECT("'" & C1 & "'" & "!A502:'" & C1 & "'" & "!A503"))
Любая помощь в том, где я делаю ошибку, пожалуйста?
Правильная формула у вас там будет:
=SUM(INDIRECT("'"&C1&"'!A502:A503"))
Я протестировал это и подтвердил, что он работает, когда C1 говорит “Лист 2”, и у меня есть другой лист под названием “Лист 2”.
Две вещи, которые я изменил:
Сначала вы ссылались на него так, чтобы он повторял имя листа для первой ячейки, указанной в диапазоне (A502), а также во второй ячейке (A503). Но если вы вручную выберите A502: A503 в формуле excel, вы увидите, что она принимает форму: “Имя листа”! A502: A503.
Я также упростил формулу, так что “” находится в той же строке, что и “!”. Однако это не сработало бы без этого изменения.
Ключом к динамическому созданию таких формул (с использованием INDIRECT или VBA) является то, что вы действительно вернетесь в Excel и попробуйте ввести его вручную – что делает Excel в качестве приемлемой формулы? Также – если вы хотите понять, почему формула не работает, перейдите к ленте “Формулы” и нажмите “Оценить формулу”. Это позволит вам шаг за шагом использовать формулу, поскольку она упрощается. Если бы вы сделали это здесь, это показало бы вам, какова была окончательная ссылка, которая была создана вашей формулой, и вы могли бы сравнить это с вашей собственной созданной вручную версией.