Defined Range | Validation List

[Tom]

I need some help with defined ranges and as they relate to drop-down menus.


SCENARIO:
I have 2 worksheets (Input, SourceData).

The "SourceData" worksheet contains defined ranges.

For instance, I have a range called "Test" which is referenced to
"=Input!$A$2:$A$50".

On the "Input" worksheet, I then use a drop-down menu (Validation | List) in
cells
A2:A50. The source for this list is "=Test".

All EXISITING or NEWLY ENTERED values into cells A2:A50 (Input worksheet)
will appear in the drop-down.



QUESTION:

Here's my problem... and what I'd like to achieve:

1. The values are currently sorted based on the row number. Instead, I'd
like to sort
them in ASC order. Is there a way to do that?

2. Some cells (between A2:A50) may not be populated. Any empty cells (e.g.
A17:A20 may not have a value) are represented by blank lines in the
drop-down menu. Does anyone know how to NOT show blank lines in the
drop-down box?


Thanks in advance,
Tom

 

[Frank Kabel]

Hi
this could only be done (IMHO) using a helper column with a formla
which sorts your original sorce range. Use this helper column as your
data source for data validation.

See the following thread for some solutions to sort a range with
worksheet functions:
http://tinyurl.com/yv2lq

One question though: Why not simply sort your range after entering some
values?

 

[Tom]

Thanks for the info.

P.S. The data in the Input column should not be sorted... however, the
options to populate the data (in the drop-down) should be in sorted order.

Thanks again,
Tom