Drop down list returning another list element

[gcefaloni]

Hi guys,

I deal with a lot of weird identifiers that I can never remember and I was wondering if it would be possible to create a dropdown list that returns an element from an adjacent list. So if you refer to the list below as an example, let's say my drop-down list would be counterparty (names that I recognize) but when I actually click on one of those list elements, the adjacent element from the "CUID" or "BIC" column would populate (numbers I can never remember by heart) in its place. How would I go about doing that?

[TABLE="width: 334"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]COUNTERPARTY[/TD]
[TD]CUID[/TD]
[TD]BIC[/TD]
[/TR]
[TR]
[TD]NESS[/TD]
[TD]N987T754I[/TD]
[TD]N235E983SS[/TD]
[/TR]
[TR]
[TD]CASGRAIN[/TD]
[TD]CAS9123365[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CIBC[/TD]
[TD]C6I851B9C7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DESJ [/TD]
[TD]V213M78DM[/TD]
[TD]V1A4M4D6CAM1[/TD]
[/TR]
[TR]
[TD]LAUR[/TD]
[TD]L75A66U9R1[/TD]
[TD]BL8C4MCA8M[/TD]
[/TR]
[TR]
[TD]NBF[/TD]
[TD]N9B323612S[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RBC[/TD]
[TD]DOM7854A[/TD]
[TD]RB1D7S6CAT1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Thanks!!

 

[Akuini]

Say you have the list ("COUNTERPARTY CUID BIC") in Sheet2 col A:C and you want to insert data in Sheet1 col A:C.
Use this code, put it in the code window of sheet1.
Everytime you insert data in sheet1 col A it will automatically insert the data (from the list) to the adjacent cell (col B:C).

[FONT=lucida console][COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] Worksheet_Change([COLOR=Royalblue]ByVal[/COLOR] Target [COLOR=Royalblue]As[/COLOR] Range)
[COLOR=Royalblue]Dim[/COLOR] va

[COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] Intersect(Target, Range([COLOR=brown]"A:A"[/COLOR])) [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]Then[/COLOR]

    [COLOR=Royalblue]With[/COLOR] Sheets([COLOR=brown]"Sheet2"[/COLOR])
    va = .Range([COLOR=brown]"A2:C"[/COLOR] & .Cells(.Rows.Count, [COLOR=brown]"A"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp).Row)
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]
    
    [COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR])
        [COLOR=Royalblue]If[/COLOR] Trim(LCase(Target.Value)) = Trim(LCase(va(i, [COLOR=crimson]1[/COLOR]))) [COLOR=Royalblue]Then[/COLOR]
            Target.Offset(, [COLOR=crimson]1[/COLOR]) = va(i, [COLOR=crimson]2[/COLOR])
            Target.Offset(, [COLOR=crimson]2[/COLOR]) = va(i, [COLOR=crimson]3[/COLOR])
        [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
    [COLOR=Royalblue]Next[/COLOR]


[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

 

[gcefaloni]

Say you have the list ("COUNTERPARTY CUID BIC") in Sheet2 col A:C and you want to insert data in Sheet1 col A:C.
Use this code, put it in the code window of sheet1.
Everytime you insert data in sheet1 col A it will automatically insert the data (from the list) to the adjacent cell (col B:C).

[FONT=lucida console][COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] Worksheet_Change([COLOR=Royalblue]ByVal[/COLOR] Target [COLOR=Royalblue]As[/COLOR] Range)
[COLOR=Royalblue]Dim[/COLOR] va

[COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] Intersect(Target, Range([COLOR=brown]"A:A"[/COLOR])) [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]Then[/COLOR]

    [COLOR=Royalblue]With[/COLOR] Sheets([COLOR=brown]"Sheet2"[/COLOR])
    va = .Range([COLOR=brown]"A2:C"[/COLOR] & .Cells(.Rows.Count, [COLOR=brown]"A"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp).Row)
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]
    
    [COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR])
        [COLOR=Royalblue]If[/COLOR] Trim(LCase(Target.Value)) = Trim(LCase(va(i, [COLOR=crimson]1[/COLOR]))) [COLOR=Royalblue]Then[/COLOR]
            Target.Offset(, [COLOR=crimson]1[/COLOR]) = va(i, [COLOR=crimson]2[/COLOR])
            Target.Offset(, [COLOR=crimson]2[/COLOR]) = va(i, [COLOR=crimson]3[/COLOR])
        [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
    [COLOR=Royalblue]Next[/COLOR]


[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

Thanks. I'll try to figure this out using vba as you are suggesting but I am not very familiar with it. Is there a way to do the same thing using Excel functions?

 

[Akuini]

Thanks. I'll try to figure this out using vba as you are suggesting but I am not very familiar with it. Is there a way to do the same thing using Excel functions?

Yes, but I only know a little about Excel formula, so maybe someone else here would help you with that.

 

[gcefaloni]

Any way for this to work using a combination of Excel functions?