1

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.

  • As the formula in column 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. – Tim Wilkinson Apr 6 '16 at 12:39
  • I am not following what you are trying to accomplish. Are you trying to return the values of the formulas if there is a result, otherwise leave it blank? Why are you using OFFSET? – CharlieRB Apr 6 '16 at 12:47
  • It is for use in the Name Manager to be used for a data validation drop down list elsewhere. – Tim Wilkinson Apr 6 '16 at 12:56
  • Don't try to think of the whole process at one time. First things first...you are trying to return only the results of the formulas in column A, correct? Why are you using OFFSET? – CharlieRB Apr 6 '16 at 13:18
  • Correct, the whole column A is formulas, and I only want the results that you can "see". I am using OFFSET because that is how to create a DNR, excel-easy.com/examples/dynamic-named-range.html – Tim Wilkinson Apr 6 '16 at 13:27
2

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

1

Instead of
COUNTA(Projects!$A:$A)
use
COUNTIF(Projects!$A:$A,"<>"&"").

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

A cell containing a formula, even if it returns "" isn't empty.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.