Excel Ignore Blanks in Data Validation List

Excel Ignore Blanks in Data Validation List

Last week I showed you how you can extract a list that excludes blank cells for use in a data validation list.

excel extract a list exluding blanks

Using this array formula in cells C2:C10:

=IFERROR(INDEX($A$2:$A$10,SMALL(IF(ISTEXT($A$2:$A$10),ROW($A$1:$A$9),""), ROW(A1))),"")

Aside: Roberto from Excel blog E90E50 pointed out to me that we can actually leave out the “” from the SMALL(IF part of the formula as the SMALL function doesn’t consider the Boolean values. So we can shorten the formula to this:

=IFERROR(INDEX($A$2:$A$10,SMALL(IF(ISTEXT($A$2:$A$10),ROW($A$1:$A$9)), ROW(A1))),"")

Entered with CTRL+SHIFT+ENTER as it’s an array formula.

OK moving on, now that we have a list in column C without blanks we want to use it as the Source of our Data Validation List, and we want to allow for growth in the list, which means it needs to be dynamic.

It’d be nice if we could use one of the ways to create dynamic named ranges I showed you a couple of weeks ago, but we can’t :(

This is because those examples use the COUNTA function, and COUNTA counts blanks returned from formulas.

Which means it would include cells C8:C10 because although they appear blank they actually evaluate to blank using the IFERROR part of the formula.

When a Blank Cell Isn’t Really Blank

excel extract a list exluding blanks

excel extract a list exluding blanks

We therefore need to use a slightly different formula that excludes blank cells (both real and fake) from the count.

Note: ‘fake blank cell’ is not a technical term :).

Exclude Blanks in a Dynamic Named Range

We can use the following formula to dynamically calculate the named range that excludes both fake blank cells (blanks generated from a formula), and real blank/empty cells:

=Sheet1!$C$2:INDEX(Sheet1!$C$2:$C$1000,SUMPRODUCT(--(Sheet1!$C$2:$C$1000<>"")))

In English this reads:

Return a range that starts in cell C2 through to the last cell in the range C2:C1000 that isn’t blank.

Which evaluates to:

Sheet1!$C$2:$C$7

We can give the formula a name (listIndex) in the name manger:

excel extract a list exluding blanks

And use the name as the source for our Data Validation list:

excel extract a list exluding blanks

Let’s inspect this formula a little closer:

=Sheet1!$C$2:INDEX(Sheet1!$C$2:$C$1000,SUMPRODUCT(--(Sheet1!$C$2:$C$1000<>"")))

The starting point of the range is fixed:

Sheet1!$C$2:

The second part is resolved using the INDEX function.

Remember the syntax for INDEX is:

=INDEX(reference, row_num, [column_num], [area_num])

And we’re only using the first two arguments in this formula.

First the SUMPRODUCT part:

excel extract a list exluding blanks

Our formula therefore evaluates SUMPRODUCT like this:

=Sheet1!$C$2:INDEX(Sheet1!$C$2:$C$1000,6)

Since there are 6 cells in the range C2:C1000 that are not blank.

And since C7 is the 6th in the range C2:C1000 INDEX evaluates like this:

=Sheet1!$C$2:$C$7

Download the workbook

This Excel workbook contains the solutions discussed in last week’s blog post on extracting a list that excludes blank cells, plus this week’s dynamic named range that excludes blanks.

Note: this is a .xlsx file, please ensure your browser doesn’t change the file extension when you download it.

Bonus: See sheet ‘r’ in this workbook for an alternative solution provided by Roberto that hides errors using conditional formatting instead of IFERROR, and therefore uses a different formula for the named range used in the data validation list.

Thanks for sharing, Roberto :)

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Current ye@r *

Comments

  1. René says

    Hi Mynda,

    As an alternative way for checking on “”, you could also check on lenght of the content of the cell. The SUMPRODUCT part of your formula would read:

    SUMPRODUCT(–(LEN(Sheet1!$C$2:$C$1000)>0))

    It’s not a big thing, just another way of reaching the same goal.

    kind regards,
    René

  2. Ben says

    If you don’t want to worry about using name manager then you can type the following directly in to the source box for the list.

    =INDIRECT(CELL("address",Sheet1!$C$2) & ":" & CELL("address",INDEX(Sheet1!$C$2:$C$1000,SUMPRODUCT(--(Sheet1!$C$2:$C$1000"")))))

    Great walk-though.

    • Mynda Treacy says

      Thanks for sharing, Ben.

      The only downside is it results in blanks at the end of the list. Not a biggy, but it would be nice if it didn’t.

      Cheers,

      Mynda

  3. Roi Mintz says

    Hi,

    I just wanted to say huge thanks for your incredible solution, to a problem that was seemed to have no solution at all.

    It is really amazing!

    Thanks a lot!
    Roi

  4. SWilliams says

    Hello,
    In Excel 2007 I am working
    Column A = Name Range
    Column B = % of cells that could be a value or “0″ or blank or errors
    Column V = Single Name

    I need to include the “0″ values but ignore the blanks
    here is my formula which is partially working -except for ignoring the blank cells. Can you assist?

    =IFERROR(SUMIF(A:A, matches a name in column v,tell me what the value in Column B is),”-”) ——– how would I insert a piece to ignore blanks?

    Thank you for your help
    S

    • Catalin Bombea says

      Hi,
      Can you please upload a sample workbook with your data structure on our Help Desk? It will be a lot easier for us to understand your situation and to assist you in this problem. Any detail you can give is important: you need to include the “0″ but ignore the blanks where? In a data validation list?
      I’ll wait for the file :)
      Cheers,
      Catalin

  5. Tim says

    Hi,
    I’m just starting to understand this and it’s great but why can’t I get it to work using both text and numbers in my list? I’ve tried variations, ISBLANK, or(istext,isnumber). I just don’t know enough about how excel works. Any help would be appreciated

    • Mynda Treacy says

      Hi Tim,

      If you download the file available in the blog post above (here it is again) you can use the ‘Not Blank Formula’ (column E) and the data validation list that uses the OFFSET formula (column K).

      Let me know if you get stuck.

      Kind regards,

      Mynda.

    • Stan Ludovyski says

      Hi there,

      This method works perfectly for lists which are just manually entered values. However, I have a list which is populated based on the result of a formula [that formula being =IF(L9=TRUE,C9,"")], and I am trying to remove the blanks that result in this list from my drop down list. They are “false blanks” as you named them above, and I am having trouble modifying your formula to suit. Currently, the new list generated has the same blanks in the same spots.

      Is anyone able to help out with this one?

      Many thanks!