0

Apparently, blanks and empty strings are two different things in Excel. When I want to use a range of cells that contains empty strings for an in-cell data validation dropdown and tell it to ignore blanks, it still shows the empty strings.

You can reproduce this by entering ="" in cell A1 and then entering =ISBLANK(A1) in another cell and it will return FALSE. Delete the formula from A1 and it returns TRUE.

Would be nice if there were a =BLANK() function.

I've also tried returning =NA(), but it turns out that the dropdown list doesn't ignore errors, either.

  • can you sort your range? – Máté Juhász Feb 8 '18 at 20:31
  • @MátéJuhász I could, but I don't want to because I have four separate ranges that reference cells from four other worksheets. This question is sort of an extension to this question: superuser.com/questions/1293220/… – oscilatingcretin Feb 8 '18 at 20:33
1

A data validation dropdown isn't going to cooperate with you on this. It only wants to look at a static list, or at exactly the cells you tell it to, without thinking about what's in them.

Since we can't make it do what we want, let's compromise - we'll give it a continuous range that shows only the values we want to see (plus the blanks at the end, where people mostly won't look).

In my example, the cells that contain the values (and blanks) for my data validation list are A1:A15. I'm going to go to another column and type the following formula:

=IFERROR(INDEX($A$1:$A$15,SMALL(IF($A$1:$A$15<>"",ROW($A$1:$A$15),999),ROW(1:1))),"")

I need to enter this as an array formula, so I'll press CTRL + SHIFT + ENTER when I'm done typing the formula. Next, I'll copy/paste or fill down 15 rows. The result will be all of my non-blank values from A1:A15, then all my blanks at the end.

Now, I'll open the Data Validation dialog and point my list at the cells containing my formulas (not A1:A15). The blanks are still there, but at the end, so no one needs to see them unless they scroll down there to look at nothing.

It's not entirely clean, but it's dynamic and avoids macros.


So what did that formula do?

=INDEX($A$1:$A$15,

looks at the original range that has your values and blanks, and lets you tell it which cell you want to look at, by number.

SMALL(

looks at a set of numbers, counts up from the smallest, and gives one back,

IF($A$1:$A$15<>"",

checks whether each cell in your range matches anything but a blank string

ROW($A$1:$A$15)

returns the row number if it does,

,999),

and returns 999 if it doesn't. If you had more than 1000 rows, you'd need a bigger number here, but this is (hopefully) more than enough for your data validation choices. The SMALL function now has a list of row numbers, with 999 in place of the actual row number for any blanks.

ROW(1:1)

is a dynamic way to start counting from whatever cell you enter this formula in. As you copy the formula down, the number grows. This tells the small that you want the 1st smallest number in the 1st row containing the formula, the 2nd smallest in row 2, etc.

The SMALL hands the row number back to INDEX, which gives you the value for that row. Those 999s all turn into #REF errors at the end, but we quietly turn those back to blanks with the IFERROR.

That formula can be tricky to understand. If you're having trouble, try setting up sample data in A1:A15 on a new sheet, picking a cell in another column, and copying my formula exactly into the formula bar (don't forget CTRL+SHIFT+ENTER). Play with that a bit, and you should get the hang of it.

Good luck!

  • 1
    That's certainly some Excel wizardry that I still don't understand even after your detailed explanation. It works, but the only thing I don't like about it is how the dropdown defaults to the first blank in the list if the cell is blank (it's just matching the first value it finds). I will leave this question open for a few more days and mark yours as answer if no one can come up with another solution. Edit: Just figured out that having the expression return " " (a space) will prevent the first blank from auto-selecting. – oscilatingcretin Feb 12 '18 at 1:25
0

Will something like this work?

=IF(AND(NOT(ISFORMULA(J3)),J3=""),"Truly Empty","Blank via Formula")

Note: if you want a custom function, like doing literally =blank(A1), you'll need VBA to make a UDF. The above formula checks if the cell you're looking in has a formula and is also blank.

  • I bet if you wrap =ISBLANK() around that entire expression, it will return FALSE – oscilatingcretin Feb 8 '18 at 21:29

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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