How to link data from one drop-down list to another

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dolfijntje33 - 19 May 2006 11:07 GMT

Hi,

I Excel 2003 I have 3 drop-down lists.
In list one is 1,2,3.
In list 2 it is A,B,C.
In list three it is $,€,*.

Which function do I need to link 1 (from list one), to B (in list two),
to $ (in list three). So if I select 1 in list one B will automtically
appear in list two and $ automatically in list three.

Can anyone help me to solve this?:confused:

Signature

dolfijntje33

Reply to this Message

Bob Phillips - 19 May 2006 11:46 GMT

See http://www.contextures.com/xlDataVal02.html

Signature

HTH

Bob Phillips

(remove xxx from email address if mailing direct)

> Hi,
>
[quoted text clipped - 8 lines]
>
> Can anyone help me to solve this?:confused:

Reply to this Message

dolfijntje33 - 19 May 2006 12:53 GMT

I already seen that solution. Unfortunatly that is nog what I mean. I
that example you still need to make a selection in list 2 and/or 3. I
my case I want to make a selection in list one and that list 2 and lis
3 automatically sets the value that was give to the list item selecte
in list one.
So...
If in list one "1" is selected, in list 2 and 3 automatically th
values B and $ will appear without having to use the drop-down lis
there

Reply to this Message

Bob Phillips - 19 May 2006 13:28 GMT

You would need VBA for that, and your lists would have to be in ranges. Is
that the case for you?

Signature

HTH

Bob Phillips

(remove xxx from email address if mailing direct)

> I already seen that solution. Unfortunatly that is nog what I mean. In
> that example you still need to make a selection in list 2 and/or 3. In
[quoted text clipped - 5 lines]
> values B and $ will appear without having to use the drop-down list
> there.

Reply to this Message

Bob Phillips - 19 May 2006 13:35 GMT

Here is an example of the code in such a case

Option Explicit

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Address = "$A$1" Then
.Offset(0, 1).Value = Application.Index(Range("List2"), _
Application.Match(.Value, Range("List1"), 0))
.Offset(0, 2).Value = Application.Index(Range("List3"), _
Application.Match(.Value, Range("List1"), 0))
ElseIf .Address = "$B$1" Then
.Offset(0, -1).Value = Application.Index(Range("List1"), _
Application.Match(.Value, Range("List2"), 0))
.Offset(0, 1).Value = Application.Index(Range("List3"), _
Application.Match(.Value, Range("List2"), 0))
ElseIf .Address = "$C$1" Then
.Offset(0, -2).Value = Application.Index(Range("List1"), _
Application.Match(.Value, Range("List3"), 0))
.Offset(0, -1).Value = Application.Index(Range("List2"), _
Application.Match(.Value, Range("List3"), 0))
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

Signature

HTH

Bob Phillips

(remove xxx from email address if mailing direct)

> You would need VBA for that, and your lists would have to be in ranges. Is
> that the case for you?
[quoted text clipped - 11 lines]
> http://www.excelforum.com/member.php?action=getinfo&userid=34593
> > View this thread: http://www.excelforum.com/showthread.php?threadid=543659

Reply to this Message

dolfijntje33 - 19 May 2006 14:51 GMT

If this example code is correct as I cannot read VBA script.:-

Reply to this Message

Bob Phillips - 19 May 2006 19:40 GMT

If you name the lists as List1, List2, and List3 and the DV is in A1, B1 and
C1, then yes.

Signature

HTH

Bob Phillips

(remove xxx from email address if mailing direct)

> If this example code is correct as I cannot read VBA script.:-(

Reply to this Message