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

Share This

Please share this or leave a comment and I'll make sure you get a personal reply.

Leave a Comment

Current day month ye@r *

{ 4 comments… read them below or add one }

SWilliams May 24, 2014 at 3:48 am

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

Reply

Catalin Bombea May 24, 2014 at 2:00 pm

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

Reply

Tim October 1, 2013 at 1:58 pm

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

Reply

Mynda Treacy October 1, 2013 at 7:47 pm

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.

Reply

Previous post:

Next post: