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