Get rid of empty cells when displaying Data validation list
 

My Data Validation list includes empty cells. I would like to get rid of
those empty cells when this list is displayed when I click the arrow to show
the valid entries.



Thanks

Michel

Get rid of empty cells when displaying Data validation list
 

One solution would be to go to the cell range that is referenced by
your data validation list and sort it. That would move empty cells to
the bottom of the list, out of the way.

Get rid of empty cells when displaying Data validation list
 

Thanks Dave,
Yes, but that list is serving another purpose the way it is.This validion
list is what I call "Type" and the next column list the valid "Sub-type" for
each Type. The number of empty cells correspond to the number of sub-types
for each type.

"Dave O" wrote in message
ups.com...
One solution would be to go to the cell range that is referenced by
your data validation list and sort it. That would move empty cells to
the bottom of the list, out of the way.

Get rid of empty cells when displaying Data validation list
 

Is this because you've reserved extra rows so that you can plop in additional
entries without worring about changing the data|validation list.

If yes, you may want to consider naming your range and use the techniques at
Debra Dalgleish's site:
http://www.contextures.com/xlNames01.html#Dynamic

You can create this dynamic range name that will grow and expand based on the
number of entries in the list.

mbeauchamp wrote:

My Data Validation list includes empty cells. I would like to get rid of
those empty cells when this list is displayed when I click the arrow to show
the valid entries.

Thanks

Michel

--

Dave Peterson

Get rid of empty cells when displaying Data validation list
 

Thanks Dave P.

Not really, althouth I do add occasionnally some types or sub-types, but
this is not my concern here.
What I am trying to do is to get my Data validation list of Types to appear
consecutively without the blank cells in between. I am using a Range name to
define my list of Types, and I am using the INDIRECT() function in my
validation to point to my list of types which is on a separate sheet.

Here is what my validation list looks like:
Col A Col B
------ ------
Type1
Sub-type1
Sub-type2
Sub-type3
Sub-type4
Type2
Sub-type10
Sub-type11
Type3
Sub-type20
Sub-type21
etc...

When I enter data and I click the arrow to see the valid Type entries, I
would like to see my entries without showing the blank cells between each
type:
Type1
Type2
Type3
etc...

I hope this will clarify.
Michel

"Dave Peterson" wrote in message
...
Is this because you've reserved extra rows so that you can plop in
additional
entries without worring about changing the data|validation list.

If yes, you may want to consider naming your range and use the techniques
at
Debra Dalgleish's site:
http://www.contextures.com/xlNames01.html#Dynamic

You can create this dynamic range name that will grow and expand based on
the
number of entries in the list.

mbeauchamp wrote:

My Data Validation list includes empty cells. I would like to get rid of
those empty cells when this list is displayed when I click the arrow to
show
the valid entries.

Thanks

Michel

--

Dave Peterson

Get rid of empty cells when displaying Data validation list
 

I would rearrange my data so that I had:

ColA ColB (subtype1) colC(Next subtype) etc....
Type1 sub-type1 sub-type10
Type2 sub-type2 sub-type11
Type3 sub-type3 sub-type12
sub-type4 sub-type13
sub-type14


Then I could use the techniques at Debra's site:
http://www.contextures.com/xlDataVal02.html
for dependent lists.




mbeauchamp wrote:

Thanks Dave P.

Not really, althouth I do add occasionnally some types or sub-types, but
this is not my concern here.
What I am trying to do is to get my Data validation list of Types to appear
consecutively without the blank cells in between. I am using a Range name to
define my list of Types, and I am using the INDIRECT() function in my
validation to point to my list of types which is on a separate sheet.

Here is what my validation list looks like:
Col A Col B
------ ------
Type1
Sub-type1
Sub-type2
Sub-type3
Sub-type4
Type2
Sub-type10
Sub-type11
Type3
Sub-type20
Sub-type21
etc...

When I enter data and I click the arrow to see the valid Type entries, I
would like to see my entries without showing the blank cells between each
type:
Type1
Type2
Type3
etc...

I hope this will clarify.
Michel

"Dave Peterson" wrote in message
...
Is this because you've reserved extra rows so that you can plop in
additional
entries without worring about changing the data|validation list.

If yes, you may want to consider naming your range and use the techniques
at
Debra Dalgleish's site:
http://www.contextures.com/xlNames01.html#Dynamic

You can create this dynamic range name that will grow and expand based on
the
number of entries in the list.

mbeauchamp wrote:

My Data Validation list includes empty cells. I would like to get rid of
those empty cells when this list is displayed when I click the arrow to
show
the valid entries.

Thanks

Michel

--

Dave Peterson

--

Dave Peterson

Get rid of empty cells when displaying Data validation list
 

Thank you Dave P.

I guess it means giving up the sort feature when I add new sub-types.

I will consider this.

"Dave Peterson" wrote in message
...
I would rearrange my data so that I had:

ColA ColB (subtype1) colC(Next subtype) etc....
Type1 sub-type1 sub-type10
Type2 sub-type2 sub-type11
Type3 sub-type3 sub-type12
sub-type4 sub-type13
sub-type14


Then I could use the techniques at Debra's site:
http://www.contextures.com/xlDataVal02.html
for dependent lists.




mbeauchamp wrote:

Thanks Dave P.

Not really, althouth I do add occasionnally some types or sub-types, but
this is not my concern here.
What I am trying to do is to get my Data validation list of Types to
appear
consecutively without the blank cells in between. I am using a Range name
to
define my list of Types, and I am using the INDIRECT() function in my
validation to point to my list of types which is on a separate sheet.

Here is what my validation list looks like:
Col A Col B
------ ------
Type1
Sub-type1
Sub-type2
Sub-type3
Sub-type4
Type2
Sub-type10
Sub-type11
Type3
Sub-type20
Sub-type21
etc...

When I enter data and I click the arrow to see the valid Type entries, I
would like to see my entries without showing the blank cells between each
type:
Type1
Type2
Type3
etc...

I hope this will clarify.
Michel

"Dave Peterson" wrote in message
...
Is this because you've reserved extra rows so that you can plop in
additional
entries without worring about changing the data|validation list.

If yes, you may want to consider naming your range and use the
techniques
at
Debra Dalgleish's site:
http://www.contextures.com/xlNames01.html#Dynamic

You can create this dynamic range name that will grow and expand based
on
the
number of entries in the list.

mbeauchamp wrote:

My Data Validation list includes empty cells. I would like to get rid
of
those empty cells when this list is displayed when I click the arrow
to
show
the valid entries.

Thanks

Michel

--

Dave Peterson

--

Dave Peterson