Data Validation List =Name

[Pitbull]

Hello all,

I have a question about Data Validation.
I set up a range which can be modified at any given time.
I gave this range a name (Hello1)
Now, on another cell I use Data Validation, List, =Hello1
It works fine, except that if I have empty rows at the end
of my range, it starts with the bottom (which shows
nothing) instead of having the drop down showing the first
few rows of my range.
In other words, I would like to have the Drop Down menu
show the first choices instead of the last choices of my
range...

Example: In E1:E20 I have a list of items, Now I Insert
Name Define E1:E30 with the name "Hello1". I would like
to have it show E1:E8 instead of E23:E30. Is there a way
to do it?

Thanks in advance for any help!
Pitbull

 

[Arvi Laanemets]

Hi

Something is wrong with your example! You must have the list from E1:E30 to
be shown.

To avoid empty entries to be displayed, use dynamic range (but you don't
have any gaps in your list hten). P.e. your list is in column E on Sheet1.
Define named range Hello1 as:
Hello1=OFFSET(Sheet1!$E$1,,,COUNTIF(Sheet1!$E:$E,"<>"),1)
(when list is without header), or
Hello1=OFFSET(Sheet1!$E$2,,,COUNTIF(Sheet1!$E:$E,"<>")-1,1)
(when list has header in cell E1)


Arvi Laanemets

 

[Debra Dalgleish]

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

Instead of leaving blanks so the list can grow, you can use a dynamic
name for the range instead. There are instructions on my web site:

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

 

[Pitbull]

Exactly what I needed, thanks a lot!

Pitbull

-----Original Message-----
Hi

Something is wrong with your example! You must have the list from E1:E30 to
be shown.

To avoid empty entries to be displayed, use dynamic range (but you don't
have any gaps in your list hten). P.e. your list is in column E on Sheet1.
Define named range Hello1 as:
Hello1=OFFSET(Sheet1!$E$1,,,COUNTIF(Sheet1!$E:$E,"<>"),1)
(when list is without header), or
Hello1=OFFSET(Sheet1!$E$2,,,COUNTIF(Sheet1!$E:$E,"<>")- 1,1)
(when list has header in cell E1)


Arvi Laanemets





.