Get rid of empty cells when displaying Data validation list

[mbeauchamp]

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 O]

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.

 

[mbeauchamp]

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 Peterson]

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]

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]

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]

Thank you Dave P.

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

I will consider this.