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

Put this in Input sheet code module

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("G15:I21")) Is Nothing Then Exit Sub
Dim L(21), List, nbr
For Each c In Range("G15:I21").SpecialCells(xlCellTypeConstants, 3)
nbr = nbr + 1: L(nbr) = c.Value
Next
For t = 1 To nbr
For t2 = t To nbr
If L(t2) < L(t) Then x = L(t): L(t) = L(t2): L(t2) = x
Next
Next
For t = 1 To nbr
List = List & L(t) & ","
Next
With Sheets("Sheet2").Range("C2").Validation ' change Sheet2 to yours
output sheet
.Delete
.Add xlValidateList, Formula1:=List
.InCellDropdown = True
End With
End Sub




"Mike Fogleman" skrev:

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

Thanks for the code, but I am looking for a non-VBA solution.
Mike F
"excelent" wrote in message
...
Put this in Input sheet code module

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("G15:I21")) Is Nothing Then Exit Sub
Dim L(21), List, nbr
For Each c In Range("G15:I21").SpecialCells(xlCellTypeConstants, 3)
nbr = nbr + 1: L(nbr) = c.Value
Next
For t = 1 To nbr
For t2 = t To nbr
If L(t2) < L(t) Then x = L(t): L(t) = L(t2): L(t2) = x
Next
Next
For t = 1 To nbr
List = List & L(t) & ","
Next
With Sheets("Sheet2").Range("C2").Validation ' change Sheet2 to yours
output sheet
.Delete
.Add xlValidateList, Formula1:=List
.InCellDropdown = True
End With
End Sub




"Mike Fogleman" skrev:

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

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

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

The data is alphanumeric like "tytin001cm9".

Ok, alphanumeric = TEXT.

This method is based on *all* user input being TEXT.

Create a 1 dimensional array from your table that's in the range G15:I21.

Enter this formula in K15 and copy down to K35 (21 rows total):

=OFFSET(G$15,INT((ROWS(G$15:G15)-1)/3),MOD(ROWS(G$15:G15)-1,3))

I've also given this range a name:

K15:K35 = Array1

Any empty cells in the user input range G15:I21 will show up as 0 in Array1.

Create a second array from Array1 sorted ascending.

Enter this array formula** in L15 and copy down to L35:

=INDEX(Array1,MATCH(SMALL(IF(Array1<0,COUNTIF(Arr ay1,"<"&Array1)),ROWS(L$15:L15)),IF(Array1<0,COUN TIF(Array1,"<"&Array1)),0))

I've also given this range a name:

L15:L35 = Array2

Any empty cells in the user input range G15:I21 will show up as #NUM! errors
in Array2

Set up your data validation list(s). You said you want to use this list on
other sheets so you'll have to give the source a defined name.

Goto InsertNameDefine
Name: List1
Refers to: =OFFSET(Sheet1!$L$15,,,COUNT(SEARCH("*",array2)))
OK

As the source for the validation list(s) use: =List1

Of course you can hide Array1 and Array2 so no one can see them.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Mike Fogleman" wrote in message
...
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

A brilliant piece of work!! Thanks for the effort.

Mike F
"T. Valko" wrote in message
...
The data is alphanumeric like "tytin001cm9".

Ok, alphanumeric = TEXT.

This method is based on *all* user input being TEXT.

Create a 1 dimensional array from your table that's in the range G15:I21.

Enter this formula in K15 and copy down to K35 (21 rows total):

=OFFSET(G$15,INT((ROWS(G$15:G15)-1)/3),MOD(ROWS(G$15:G15)-1,3))

I've also given this range a name:

K15:K35 = Array1

Any empty cells in the user input range G15:I21 will show up as 0 in
Array1.

Create a second array from Array1 sorted ascending.

Enter this array formula** in L15 and copy down to L35:

=INDEX(Array1,MATCH(SMALL(IF(Array1<0,COUNTIF(Arr ay1,"<"&Array1)),ROWS(L$15:L15)),IF(Array1<0,COUN TIF(Array1,"<"&Array1)),0))

I've also given this range a name:

L15:L35 = Array2

Any empty cells in the user input range G15:I21 will show up as #NUM!
errors in Array2

Set up your data validation list(s). You said you want to use this list on
other sheets so you'll have to give the source a defined name.

Goto InsertNameDefine
Name: List1
Refers to: =OFFSET(Sheet1!$L$15,,,COUNT(SEARCH("*",array2)))
OK

As the source for the validation list(s) use: =List1

Of course you can hide Array1 and Array2 so no one can see them.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Mike Fogleman" wrote in message
...
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

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Mike Fogleman" wrote in message
...
A brilliant piece of work!! Thanks for the effort.

Mike F
"T. Valko" wrote in message
...
The data is alphanumeric like "tytin001cm9".

Ok, alphanumeric = TEXT.

This method is based on *all* user input being TEXT.

Create a 1 dimensional array from your table that's in the range G15:I21.

Enter this formula in K15 and copy down to K35 (21 rows total):

=OFFSET(G$15,INT((ROWS(G$15:G15)-1)/3),MOD(ROWS(G$15:G15)-1,3))

I've also given this range a name:

K15:K35 = Array1

Any empty cells in the user input range G15:I21 will show up as 0 in
Array1.

Create a second array from Array1 sorted ascending.

Enter this array formula** in L15 and copy down to L35:

=INDEX(Array1,MATCH(SMALL(IF(Array1<0,COUNTIF(Arr ay1,"<"&Array1)),ROWS(L$15:L15)),IF(Array1<0,COUN TIF(Array1,"<"&Array1)),0))

I've also given this range a name:

L15:L35 = Array2

Any empty cells in the user input range G15:I21 will show up as #NUM!
errors in Array2

Set up your data validation list(s). You said you want to use this list
on other sheets so you'll have to give the source a defined name.

Goto InsertNameDefine
Name: List1
Refers to: =OFFSET(Sheet1!$L$15,,,COUNT(SEARCH("*",array2)))
OK

As the source for the validation list(s) use: =List1

Of course you can hide Array1 and Array2 so no one can see them.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Mike Fogleman" wrote in message
...
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