Defined Range | Validation List

  • Thread starter Thread starter Tom
  • Start date Start date
T

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
 
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?
 
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
 
Back
Top