copy unique values to validation list

[buattis]

Hi all,

I've got a "backend" that looks like this:

Year
--------
2000
2000
2002
2000
2003
2004
2004
2001
2001

I'd like to create a range based on the unique values from this list
and then use this range to as validation list criteria for an input
cell in the "frontend" sheet. I know they could just auto-filter the
back end to select what year, but i don't want the users to touch the
backend.

Also, this backend will be updated constantly, so i need the range to
be redefined with every change.

Any ideas?

 

[Gary''s Student]

This is just an example that you can adapt to your needs. There are two
worksheets: frontend and backend.

Backend is your master list in column A starting in cell A2.

Frontend has the data validation list in column B starting in cell B1

The following macro automatically runs if you update the master list. It
examines the contents of the mater list and copies over all items except
duplicates and blanks. If the master list looks like:

Year
2001
2002
2002
2001
77
54
45
1
1
2
2001
77
3

then the copied list will be:

2001
2002
77
54
45
1
2
3


This macro is a worksheet event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim skip_this_one As Boolean
Dim v() As Variant
Set t = Target
Set r = Range("A:A")
If Intersect(t, r) Is Nothing Then Exit Sub
n = Cells(Rows.Count, 1).End(xlUp).Row
ReDim v(n)

For i = 1 To n
v(i) = ""
Next
v(0) = Range("A2").Value
k = 1

For i = 2 To n
skip_this_one = False
x = Cells(i, 1).Value
If x = "" Then
skip_this_one = True
End If
For j = 0 To k
If x = v(j) Then
skip_this_one = True
End If
Next
If skip_this_one Then
skip_this_one = False
Else
v(k) = x
k = k + 1
End If
Next

Application.EnableEvents = False
Set sh = Sheets("frontend")
sh.Range("B:B").Clear
For i = 1 To k
sh.Cells(i, 2).Value = v(i - 1)
Next
Application.EnableEvents = True
End Sub


It goes in the worksheet code area, not a standard module.

 

[buattis]

thanks Gary's Student

This is just an example that you can adapt to your needs. There are two
worksheets: frontend and backend.

Backend is your master list in column A starting in cell A2.

Frontend has the data validation list in column B starting in cell B1

The following macro automatically runs if you update the master list. It
examines the contents of the mater list and copies over all items except
duplicates and blanks. If the master list looks like:

Year
2001
2002
2002
2001
77
54
45
1
1
2
2001
77
3

then the copied list will be:

2001
2002
77
54
45
1
2
3

This macro is a worksheet event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim skip_this_one As Boolean
Dim v() As Variant
Set t = Target
Set r = Range("A:A")
If Intersect(t, r) Is Nothing Then Exit Sub
n = Cells(Rows.Count, 1).End(xlUp).Row
ReDim v(n)

For i = 1 To n
v(i) = ""
Next
v(0) = Range("A2").Value
k = 1

For i = 2 To n
skip_this_one = False
x = Cells(i, 1).Value
If x = "" Then
skip_this_one = True
End If
For j = 0 To k
If x = v(j) Then
skip_this_one = True
End If
Next
If skip_this_one Then
skip_this_one = False
Else
v(k) = x
k = k + 1
End If
Next

Application.EnableEvents = False
Set sh = Sheets("frontend")
sh.Range("B:B").Clear
For i = 1 To k
sh.Cells(i, 2).Value = v(i - 1)
Next
Application.EnableEvents = True
End Sub

It goes in the worksheet code area, not a standard module.
--
Gary''s Student - gsnu2007








- Show quoted text -