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 :)

FREE PDF Download
100 Excel Tips & Tricks

Excel Tips & Tricks E-Book
Just enter your details below
* indicates required

We respect your email privacy

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. 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

  2. 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

  3. 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.