How to auto-select a value from dropdown list?

Question

So I have two sheets - SheetA and SheetB.

Sheet B contains a dropdown list [Option1/2/3] and another cell that changes its value accordingly to what option is chosen from the dropdown list.

In SheetA, I would like to list down all options - 1,2,3 and their respective values referenced from SheetB.

<Sheet B>

Drop-down List Options       CellX
======================       ======
Option 1/2/3                 10/20/30 (depending on the option chosen)

<Sheet A>

Options     Value
========    ======
1           10
2           20
3           30

My thought process: If SheetA!Options=1, Value=SheetB!CellX If SheetA!Options=2, Value=SheetB!CellX

Is there any way I can autoselect the dropdown list values and return the values in SheetA?

there should be an index that you can refer to.Don't remember if it's base 0 or base 1, i.e. the first item might be index 0. — Ronnie Royston
@RonRoyston Sorry I'm really new to VBA, could you explain a little bit more? :) — jwww

reFractil reFractil · Accepted Answer · 2019-03-30T16:31:51

there...

if you want to do this in VBA, you would need to trigger an event for the cell that holds the dropdown list options.

in the VBA Editor, select your SheetA from the project tree and add the following code:

Option Explicit 
Private Sub Worksheet_Change(ByVal Target As Range)

If ThisWorkbook.Sheets("SheetA").Range("A2") = 1 Then

    ThisWorkbook.Sheets("SheetA").Range("B2") = ThisWorkbook.Sheets("SheetB").Range("B2")

ElseIf ThisWorkbook.Sheets("SheetA").Range("A2") = 2 Then

    ThisWorkbook.Sheets("SheetA").Range("B2") = ThisWorkbook.Sheets("SheetB").Range("B3")

ElseIf ThisWorkbook.Sheets("SheetA").Range("A2") = 3 Then

    ThisWorkbook.Sheets("SheetA").Range("B2") = ThisWorkbook.Sheets("SheetB").Range("B4")

End If
End If

End Sub

although... you could just use a formula like this one in your value column on SheetA:

=INDEX(SheetB!B2:B4,MATCH(A2,SheetB!A2:A4,0))

How to auto-select a value from dropdown list?

1 Answers