Ignore Blanks in Data Validation
 

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

Instead of ignoring blank cells, you could use a dynamic range as the
list source. There are instructions he

http://www.contextures.com/xlNames01.html


Ricky wrote:
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?

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Thanks Debra,
That totally did it. Works like a charm.

Thanks again,
Ricky

"Debra Dalgleish" wrote in message
...
Instead of ignoring blank cells, you could use a dynamic range as the
list source. There are instructions he

http://www.contextures.com/xlNames01.html


Ricky wrote:
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?

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

You're welcome. Thanks for letting me know that it worked.

Ricky wrote:
Thanks Debra,
That totally did it. Works like a charm.

Thanks again,
Ricky

"Debra Dalgleish" wrote in message
...

Instead of ignoring blank cells, you could use a dynamic range as the
list source. There are instructions he

http://www.contextures.com/xlNames01.html


Ricky wrote:

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?

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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


"Debra Dalgleish" wrote in message
...
You're welcome. Thanks for letting me know that it worked.

Ricky wrote:
Thanks Debra,
That totally did it. Works like a charm.

Thanks again,
Ricky

"Debra Dalgleish" wrote in message
...

Instead of ignoring blank cells, you could use a dynamic range as the
list source. There are instructions he

http://www.contextures.com/xlNames01.html


Ricky wrote:

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?

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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?


Ricky wrote:
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


"Debra Dalgleish" wrote in message
...

You're welcome. Thanks for letting me know that it worked.

Ricky wrote:

Thanks Debra,
That totally did it. Works like a charm.

Thanks again,
Ricky

"Debra Dalgleish" wrote in message
...


Instead of ignoring blank cells, you could use a dynamic range as the
list source. There are instructions he

http://www.contextures.com/xlNames01.html


Ricky wrote:


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?

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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


"Debra Dalgleish" wrote in message
...
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?


Ricky wrote:
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


"Debra Dalgleish" wrote in message
...

You're welcome. Thanks for letting me know that it worked.

Ricky wrote:

Thanks Debra,
That totally did it. Works like a charm.

Thanks again,
Ricky

"Debra Dalgleish" wrote in message
...


Instead of ignoring blank cells, you could use a dynamic range as the
list source. There are instructions he

http://www.contextures.com/xlNames01.html


Ricky wrote:


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?

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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

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

Ricky wrote:
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


"Debra Dalgleish" wrote in message
...

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?


Ricky wrote:

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


"Debra Dalgleish" wrote in message
...


You're welcome. Thanks for letting me know that it worked.

Ricky wrote:


Thanks Debra,
That totally did it. Works like a charm.

Thanks again,
Ricky

"Debra Dalgleish" wrote in message
. ..



Instead of ignoring blank cells, you could use a dynamic range as the
list source. There are instructions he

http://www.contextures.com/xlNames01.html


Ricky wrote:



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?

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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

Thanks again Debra,
Ricky


"Debra Dalgleish" wrote in message
...
You can subtract one from the count, e.g.:

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

Ricky wrote:
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


"Debra Dalgleish" wrote in message
...

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?


Ricky wrote:

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


"Debra Dalgleish" wrote in message
...


You're welcome. Thanks for letting me know that it worked.

Ricky wrote:


Thanks Debra,
That totally did it. Works like a charm.

Thanks again,
Ricky

"Debra Dalgleish" wrote in message
. ..



Instead of ignoring blank cells, you could use a dynamic range as
the
list source. There are instructions he

http://www.contextures.com/xlNames01.html


Ricky wrote:



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?

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

You're welcome! Thanks for letting me know that you got it working.

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

Thanks again Debra,
Ricky


"Debra Dalgleish" wrote in message
...

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

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

Ricky wrote:

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


"Debra Dalgleish" wrote in message
...


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?


Ricky wrote:


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


"Debra Dalgleish" wrote in message
. ..



You're welcome. Thanks for letting me know that it worked.

Ricky wrote:



Thanks Debra,
That totally did it. Works like a charm.

Thanks again,
Ricky

"Debra Dalgleish" wrote in message
. ..




Instead of ignoring blank cells, you could use a dynamic range as

the

list source. There are instructions he

http://www.contextures.com/xlNames01.html


Ricky wrote:




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?

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html