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