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.
However this is still counting all cells with the formula in, even if they are blank. I tried using
COUNTIF to count blank cells.
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?
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.
My current work around is to count the non-blank cells in column B
Which is currently working, however for future reference I would like to know how to create the DNR from column A.