Динамический именованный диапазон Excel игнорирует пустые формулы

Question

В столбце A меня есть формула, которая объединяет два других столбца вместе.

=IF(ISBLANK(B5),"", B5&" "&C5)

Затем в Диспетчере имен - динамический именованный диапазон для использования в раскрывающемся списке проверки данных.

=OFFSET(Projects!$A$5,0,0,COUNTA(Projects!$A:$A),1)

Однако при этом учитываются все ячейки с формулой, даже если они не заполнены. Я попытался использовать COUNTIF для подсчета пустых ячеек.

=OFFSET(Projects!$A$5,0,0,COUNTIF(Projects!$A:$A, "<>"),1)

Но это все еще включает каждую ячейку с формулой, даже если она "пустая".

Есть ли способ заставить именованный диапазон игнорировать формулы, которые возвращают пустое значение?

РЕДАКТИРОВАТЬ

Ниже приведен пример того, как выглядят данные в первом бите. (начинается в строке 5, поэтому не изменяйте пример)

               A                      B          C
5 =IF(ISBLANK(B5),"", B5&" "&C5)   Director     123
6 =IF(ISBLANK(B6),"", B6&" "&C6)   Officer      321
7 =IF(ISBLANK(B7),"", B7&" "&C7)

Продолжайте до строки 1000. Столбец A скрыт, и пользователи только изменяют данные в B и C. Значения часто добавляются или удаляются, поэтому количество записей в B и C постоянно меняется.

Затем для своего DNR я пытаюсь использовать другой лист, я установил именованный диапазон с именем JobTitle, используя следующее.

=OFFSET(Projects!$A$5,0,0,COUNTIF(Projects!$A5:$A1000, "<>"),1)

Моя текущая работа заключается в подсчете непустых ячеек в столбце B

=OFFSET(Projects!$A$5,0,0,COUNTIF(Projects!$B5:$B1000, "<>"),1)

Который в настоящее время работает, однако для дальнейшего использования я хотел бы знать, как создать DNR из столбца А.

Answer 1

Я бы определил JobTitle скорее как:

=Projects!$A$5:INDEX(Projects!$A5:$A$1000,COUNTIF(Projects!$A5:$A$1000,"?*"))

который, используя INDEX вместо OFFSET , уменьшает волатильность конструкции.

Обратите внимание, что часть COUNTIF основана на предположении, что значения в диапазоне Projects!$A5:$A$1000 являются текстовыми, а не числовыми. Однако, учитывая, что каждое из значений в этом диапазоне получается путем конкатенации строк, я бы предположил, что это предположение справедливо.

С уважением

Answer 2

Вместо
COUNTA(Projects!$A:$A)
использование
COUNTIF(Projects!$A:$A,"<>"&"") .

Функция COUNTA подсчитывает количество ячеек, которые не являются пустыми в диапазоне.

Ячейка, содержащая формулу, даже если она возвращает "" , не пуста.