Dynamic/advanced data validation lists

[ExcelDane]

Hi everybody,

I have a sheet containing (among other things) a list of personal IDs in one row, and dates in another row. This list may contain more than one occurence of the same ID, but with different dates.

In another sheet, I want to be able to do like this:

1. In cell A1, enter a personal ID using a data validation dropdown list. This list should ideally contain only unique values from the list of IDs in the first sheet. Since data will continually be added to my sheet, this filtering of unique values needs to be dynamic/automatic. Of course, I can make a macro using advanced filtering to find unique values, but is there a more straightforward way to do this?

2. In cell B1, enter a date using a data validation dropdown list. This list should only contain dates that are associated (in the first sheet) with the personal ID chosen in A1. I really hope this is possible.

Any help is appreciated. I hope I made myself clear

 

[ExcelDane]

<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600">[tried to insert screendump in post, but without succes]</v:shapetype>

 

[ExcelDane]

OK, let me be a little more concrete:
In sheet1, I have a list looking like this:

<TABLE style="WIDTH: 143pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=191 border=0 x:str><COLGROUP><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4096" width=112><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 id=_x0000_s1025 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 84pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=112 height=17 x:autofilterrange="$A$1:$B$4" x:autofilter="all">Personal ID</TD><TD class=xl25 id=_x0000_s1026 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 59pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=79 x:autofilter="all">Date</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>1111</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="40210">01-02-2010</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>1111</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="40391">01-08-2010</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>2222</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="40299">01-05-2010</TD></TR></TBODY></TABLE>

In Sheet2, cell A1, I want a data validation popup containing unique personal IDs. Based on the data above, the options should be [1111;2222].

Depending on my choice in A1, the data validation popup in B1 should contain the relevant date(s).
If I select 1111, the options should be [01-02-2010;01-08-2010]. If I select 2222, the only option should be [01-05-2010].

The list in Sheet1 will not always be sorted.
Using Excel 2003.

Thanks in advance.

 

[MickG]

Hi, If the I/D's relate to the dates ,and you limit the I/Ds to Uniques, then how do then relate to the dates to the I/D's.
Because you then have one of each I/D and possibly more than one Date that relates to any one of the I/D's.
Mick
Ok, I've just seen your post !!!!

 

[ExcelDane]

I don't want to limit the original list in Sheet1. Only the data validation options in sheet2 should be limited to unique values.

Let me add that any combination of ID and date will be unique, e.g. if the same ID is represented twice, the dates will by definition be different.

 

[MickG]

Hi, Try this based on your Data in sheet (1) as shown (plus any additions)
There are two code . The first is a Selection Change event.
When you Select Sheet(2) "A1", a D/D/L is formed in "A1" showing the Uniques from sheet(1) Column "A".
When you select a Value from the list a second Code "ChangeEvent" is run producing a second D/D/L in Column "B" showing the Related dates to the Selection in "A1.
Place both code in sheet(2)
NB:- Sheet(2) Columns "BB" & BC are used to Hold Data.

Private [COLOR=navy]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR=navy]As[/COLOR] Range)
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, dn [COLOR=navy]As[/COLOR] Range, c [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]If[/COLOR] Target.Address(0, 0) = "A1" [COLOR=navy]Then[/COLOR]
    [COLOR=navy]With[/COLOR] Sheets("Sheet1")
        [COLOR=navy]Set[/COLOR] Rng = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
    [COLOR=navy]End[/COLOR] With
        ReDim ray(1 To Rng.Count)
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] dn [COLOR=navy]In[/COLOR] Rng
    [COLOR=navy]If[/COLOR] dn = Range("A1") [COLOR=navy]Then[/COLOR]
        c = c + 1
        ray(c) = dn.Offset(, 1)
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] dn
Range("BC1").Resize(c) = Application.Transpose(ray)
[COLOR=navy]Set[/COLOR] Rng = Range("BC1").Resize(c)
Range("B1").Select
    [COLOR=navy]With[/COLOR] Selection.Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:="=" & Rng.Address & ""
    [COLOR=navy]End[/COLOR] With
[COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Private [COLOR=navy]Sub[/COLOR] Worksheet_SelectionChange(ByVal Target [COLOR=navy]As[/COLOR] Range)
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] dn [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] ray [COLOR=navy]As[/COLOR] Variant
[COLOR=navy]If[/COLOR] Target.Address(0, 0) = "A1" [COLOR=navy]Then[/COLOR]
    [COLOR=navy]With[/COLOR] Sheets("Sheet1")
        [COLOR=navy]Set[/COLOR] Rng = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
    [COLOR=navy]End[/COLOR] With
[COLOR=navy]With[/COLOR] CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
        [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] dn [COLOR=navy]In[/COLOR] Rng
            .Item(dn.Value) = dn.Value
        [COLOR=navy]Next[/COLOR]
Range("BB1").Resize(.Count) = Application.Transpose(.items)
    [COLOR=navy]Set[/COLOR] Rng = Range("BB1").Resize(.Count)
[COLOR=navy]End[/COLOR] With
Range("A1").Select
    [COLOR=navy]With[/COLOR] Selection.Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:="=" & Rng.Address & ""
    [COLOR=navy]End[/COLOR] With
[COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[ExcelDane]

Wow, that looks like a lot of work.
I won't have time to try it until tomorrow. Until then, thanks a lot!

I am a bit sceptical about using worksheet_change, though. I have had some trouble making this feature work in my sheets. Sometimes it works, sometimes it doesn't, and I can't figure out any pattern.

 

[MickG]

Hi, Add the line as shown to the First bit of code to format the date !!

Range("B1").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:="=" & Rng.Address & ""
    End With
End If
Range("B1").NumberFormat = "dd/mm/yyyy" ' Add this line
End Sub

Mick