thanks Gary's Student
On Sep 27, 12:30 am, Gary''s Student
<GarysStud...@discussions.microsoft.com> wrote:
> 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
>
>
>
> "buat...@anz.com" wrote:
> > 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?
>
> > ---
> > Stefano- Hide quoted text -
>
> - Show quoted text -
|
Similar Threads
