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.
Best Answer
I would define JobTitle rather as:
=Projects!$A$5:INDEX(Projects!$A5:$A$1000,COUNTIF(Projects!$A5:$A$1000,"?*"))
which, by employing
INDEX
in place ofOFFSET
, lessens the volatility of the construction.Note that the
COUNTIF
portion rests on the assumption that the values in the rangeProjects!$A5:$A$1000
are text, not numeric. Given that each of the values within this range is derived via a string concatenation, however, I would imagine that this assumption is a fair one.Regards