In column `A`

I have a formula that collates two other columns together.

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

Then in the Name Manager, a dynamic named range to use for a data validation drop down list.

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

However this is still counting all cells with the formula in, even if they are blank. I tried using `COUNTIF`

to count blank cells.

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

But this still including every cell with a formula in, even if it is "blank".

Is there a way to get the named range to ignore formulas that return blank?

EDIT

Below is an example of how the data in the first bit looks. (starts at row 5 so kept it true to the example)

```
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 down to row 1000. Column A is hidden, and users only amend the data in B and C. Values are often added, or removed, so the number of entries in B and C change constantly

Then for my DNR I am trying to use in a different sheet I set up a Named Range called JobTitle using the following.

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

My current work around is to count the non-blank cells in column B

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

Which is currently working, however for future reference I would like to know how to create the DNR from column A.

`A`

is dependent on`B`

having data, I have changed the DNR to count if column`B`

is not blank.`=OFFSET(Projects!$A$5,0,0,COUNTIF(Projects!$B5:$B1000, "<>"),1)`

which is a suitable work around. But would still like to know how count unsatisfied formulas as blank.`OFFSET`

?`OFFSET`

?`OFFSET`

because that is how to create a DNR, excel-easy.com/examples/dynamic-named-range.html3more comments