Ignore Blanks in Data Validation

R

Ricky

Hello Experts,
I've checked the "Ignore Blanks" checkbox within Data Validation and all
blank cells are unlisted within the Dropdown list. However, when I copy and
paste as values (that does include blank cells) to replace the Source range,
the Ignore Blanks feature doesn't work anymore. I see a list of blank cells
within the bottom of my dropdown list. What is the workaround to maintain a
dropdown selection list that continues to ignore blanks?

Thanks in advance,
Ricky
 
R

Ricky

Hi again Debra,
Is there a way to make the dropdown list to default at the top of the list?
Currently, when I click on the dropdown menu, the last item (which is a
blank cell) is highlighted.

Thanks again,
Ricky
 
D

Debra Dalgleish

When the dropdown list opens, it matches the cell value to an item in
the list, if one exists. So, the blank cell matches to the blank item in
the list.

However, if you used the OFFSET formula to create a dynamic range, it
shouldn't include a blank cell. Is there another item in the column
that's adding to the count, and increasing the number of cells in the range?
 
R

Ricky

Hi Debra,
Yes you are right. There is a blank cell within the range and the dropdown
menu does point towards it...which is strange. Before I use the dropdown
list, I would always sort the data first. Then, all blank cells would end
up towards the bottom. The original offset formula works beautifully and it
does not contain a blank cell as one of the validation selection items.
=offset('Sheet1'!$N$1,0,0,counta('Sheet1'!$N:$N),1)

However, I had to vary the formula slightly to start one row below the title
because I didn't want to include the title (first row) to be amongst the
selections. This is what caused the blank cell to show up as part of the
dropdown selection items.
=offset('Sheet1'!$N$1,1,0,counta('Sheet1'!$N:$N),1)

Any workarounds to omit the blank cell? Omitting the blank cell does make
the dropdown list to default at the beginning which is exactly what I'm
looking for.

Thanks so much Debra,
Ricky
 
D

Debra Dalgleish

You can subtract one from the count, e.g.:

=offset('Sheet1'!$N$1,1,0,counta('Sheet1'!$N:$N)-1,1)
 
R

Ricky

Hello Debra,
It works beautifully! You've taught me this wonderful formula.

Thanks again Debra,
Ricky
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top