The data is alphanumeric like "tytin001cm9". This format is standard and
only the "cm" is constant. Yes I would like a non-VBA solution. This is not
my form or it would be full of code by now. I also would not have designed a
3x7 matrix for a user input list. This is an official company form for wide
distribution. Macros are forbidden. I have done several projects for them in
the past, so they approached me to put the finishing touch on this form.
This was my last hurdle.

Mike F


"T. Valko" wrote in message
...
Using a non-VBA method is somewhat complicated and will take a few steps.

Is the data text, numeric or both? This is important to know!

You'd have to extract the data into a one dimensional array (single row or
column).

Then you'd have to extract the data from the one dimensional array into
another sorted contiguous array. I guess you'd want it sorted ascending?

Then you'd have to use a named dynamic range as the source for the drop
down.

Still want to use a non-VBA method?

--
Biff
Microsoft Excel MVP


"Mike Fogleman" wrote in message
...
I have an area for user input on sheet1. This area is 7 rows by 3 columns
(G15:I21). This area is not usually fully populated, but it could be. It
also could be populated vertically or horizontally:
input1 input2 input3
input4
or
input1 input4
input2
input3

I want to use these values in a DV list on other worksheets. My problem
is getting a clean list of these values with no blanks between them.
Also, if possible, having them sorted would be a great finishing touch
for the DV drop-down.
Any suggestions would be greatly appreciated.

Mike F