CODEWITHSUNDEEP
excelvba
jwww
jwww

Reputation: 189

How to auto-select a value from dropdown list?

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?

Upvotes: 2

Views: 1933

Answers (1)

reFractil
reFractil

Reputation: 401

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))

Upvotes: 2

Related Questions