Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy unique values to validation list
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy unique values to validation list
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 " 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy unique values to validation list
thanks Gary's Student
On Sep 27, 12:30 am, Gary''s Student 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 " 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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation - List of unique values | Excel Discussion (Misc queries) | |||
Data Validation using Unique Values | Excel Discussion (Misc queries) | |||
Using automatic Unique Validation list | Excel Worksheet Functions | |||
Need UNIQUE values for Data Validation List | Excel Programming | |||
Data Validation using List (But needs unique list in drop down lis | New Users to Excel |