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?

COUNTERPARTY	CUID	BIC
NESS	N987T754I	N235E983SS
CASGRAIN	CAS9123365
CIBC	C6I851B9C7
DESJ	V213M78DM	V1A4M4D6CAM1
LAUR	L75A66U9R1	BL8C4MCA8M
NBF	N9B323612S
RBC	DOM7854A	RB1D7S6CAT1

<colgroup><col><col><col></colgroup><tbody>
</tbody>

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?