O intervalo nomeado dinâmico do Excel ignora fórmulas em branco

Question

Na coluna A , tenho uma fórmula que agrupa duas outras colunas.

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

Em seguida, no Gerenciador de nomes, um intervalo com nome dinâmico a ser usado para uma lista suspensa de validação de dados.

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

No entanto, isso ainda está contando todas as células com a fórmula, mesmo que estejam em branco. Eu tentei usar COUNTIF para contar células em branco.

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

Mas isso ainda inclui todas as células com uma fórmula, mesmo que seja "em branco".

Existe uma maneira de obter o intervalo nomeado para ignorar as fórmulas que retornam em branco?

EDITAR

Abaixo está um exemplo de como os dados no primeiro bit se parecem. (começa na linha 5, então manteve-se fiel ao exemplo)

               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)

Continue para baixo até a linha 1000. A coluna A está oculta e os usuários apenas corrigem os dados em B e C. Os valores são frequentemente adicionados ou removidos, portanto, o número de entradas em B e C muda constantemente

Então, para meu DNR que estou tentando usar em uma planilha diferente, configurei um intervalo nomeado chamado JobTitle usando o seguinte.

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

Meu trabalho atual é contar as células que não estão em branco na coluna B

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

O que está funcionando atualmente, no entanto, para referência futura, gostaria de saber como criar o DNR na coluna A.

    

Answer 1

Eu definiria JobTitle como:

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

que, empregando INDEX no lugar de OFFSET , diminui a volatilidade da construção.

Observe que a parte COUNTIF baseia-se na suposição de que os valores no intervalo Projects!$A5:$A$1000 são de texto, não numéricos. Dado que cada um dos valores dentro desse intervalo é derivado através de uma concatenação de string, no entanto, eu imagino que essa suposição é justa.

Atenciosamente

    

Answer 2

Em vez de COUNTA(Projects!$A:$A)
use COUNTIF(Projects!$A:$A,"<>"&"") .

The COUNTA function counts the number of cells that are not empty in a range.

Uma célula contendo uma fórmula, mesmo que retorne "" , não está vazia.