Excel dynamic named range ignore blank formulas

microsoft excelmicrosoft-excel-2010worksheet-function

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 of OFFSET, lessens the volatility of the construction.

Note that the COUNTIF portion rests on the assumption that the values in the range Projects!$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

Related Question