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

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

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:

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

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:

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 6^{th} 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 🙂

Jim says

Hi I am trying to mirror this formula “{=IFERROR(INDEX($A$2:$A$10,SMALL(IF(ISTEXT($A$2:$A$10),ROW($A$1:$A$9)), ROW(A1))),””)}” starting at different row and column (i.e B65 instead of A1 and so on) and the formula does not work..

I can only get it to work as long as I paste in/mirror the exact same cells in the example…

Can you think of any reasons why this is occurring? thanks in advance

Catalin Bombea says

Hi Jim,

Your formula should look like this:

=IFERROR(INDEX($B$65:$B$100,SMALL(IF(ISTEXT($B$65:$B$100),ROW($B$64:$B$99)), ROW(A1))),””)

Cherrs,

Catalin

Grant says

Just what I was looking for today! Perfect! Thanks!

Rotem Cohen says

I think you forgot to delete the “SMALL” in the “”shortened” version of the IFERROR formula.

Rotem Cohen says

Never mind. I understand what you meant now (removing the “”)

(though I can’t get any of it to work anyway.) 🙁

Mynda Treacy says

Hi Rotem,

If you’d like some help with why it’s not working you can post your workbook and question on our Excel Forum and one of us will be happy to help.

Mynda

ｊｃ ｃｈｅｏｗ says

I am trying to combine this with the formula below:

=IF(ISBLANK(!A9),Categories,OFFSET(INDEX(Categories,,MATCH(!A9,Categories,)),1,,COUNTA(OFFSET(INDEX(Categories,,MATCH(!A9,Categories,)),1,,100))))

which is from “Excel Factor 19 Dynamic Dependent Data Validation” but has not been successful.

Appreciate any help from all the experts here.

Thanks a million!!!

Mynda Treacy says

Hi JC,

The IF function returns a single value not a range. I presume ‘Categories’ is a range given it’s use in the other nested functions. Other than that I have no advice without seeing your file and you telling me what you’re hoping this formula will do.

Mynda

ｊｃ ｃｈｅｏｗ says

Hi, Mynda,

Thanks for the response. Please let me know how to attach my file.

The formula is actually used in data validation and not in a cell, so it provides a range.

Mynda Treacy says

Hi JC,

You can send your file via the Help Desk.

Please explain in your help desk ticket what you want the formula to do.

Thanks,

Mynda

ke says

I’ve modified your name manager formula, =Sheet1!$C$2:INDEX(Sheet1!$C$2:$C$1000,SUMPRODUCT(–(Sheet1!$C$2:$C$1000″”))), to fit my needs which encompasses a much larger range i.e. J4:J15000 instead of C2:C1000. More than half of that range are blanks. That equation is not working quite right as the data validation list only shows non-blank entries from J4 to about J3360ish, so there’s a lot of entries I’m unable to use in the data validation list. Why doesn’t it show everything in the full range J4:J15000? Is there a way to? Thanks in advance!

Catalin Bombea says

Hi,

Can you prepare a sample with you data and formulas?

There is no other way to help you without seeing the file, you can upload the file to our Help Desk system.

Thanks

Catalin

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é

Gourav Bhatt says

Brilliantly elegant solution! Thank you!

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

Craig Whelan says

Brilliantly elegant solution! Thank you!

Mynda Treacy says

Thanks, Craig. Glad you liked it.

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

Mynda Treacy says

You’re most welcome, Roi 🙂

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

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!

Mynda Treacy says

Hi Stan,

It’s tricky without seeing your file. Are you able to send it to us via the Help Desk?

Cheers,

Mynda

Luca says

Hi, for some reason the list has the right number of non empty entries but it is only showing the first the remaining are blank?

L.E.:

Was a solution found for formula created values?

Thanks!

Catalin Bombea says

Hi Luca,

Simply replace from the formula the ISBLANK($A$2:$A$9) with LEN($A$2:$A$9)=0 and press Ctrl+Shift+Enter, this will take care of both situations, for blanks and zero length strings “”

The final formula should look like this:

`=IFERROR(INDEX($A$2:$A$9, SMALL(IF(LEN($A$2:$A$9)=0,"", ROW($A$2:$A$9)-MIN(ROW($A$2:$A$9))+1), ROW(A1))),"")`

Cheers,

Catalin