Can selection from text drop-down dictate value in another cell

  • Thread starter Thread starter Relating text to values in drop-downs
  • Start date Start date
R

Relating text to values in drop-downs

I am working in Excel. I have created a text drop-down menu in one column.
Can I insert a function so that when a choice is made, a value automatically
appears in another cell i.e. choosing Monday in Cell D1 brings up 100 in cell
E1, choosing Tuesday in Cel D1 brings up 200 in E1 etc...
 
Yep. Take a look at LOOKUP functions (or VLOOKUP). If you're just using days,
you could even use a combination of WEEKDAY & CHOOSE. See XL's help file for
guidance on all of these functions.
 
I have a sample workbook with the exact setup you are asking....email
me and I'll send it to you!
Ken
 
Or maybe you could use VBA:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long
r = ActiveCell.Row

If Target.Column = 4 And Target.Row > 1 Then
Select Case Cells(r, "D").Value
Case "Monday"
Cells(r, "E").Value = 100
Case "Tuesday"
Cells(r, "E").Value = 200
Case "Wednesday"
Cells(r, "E").Value = 300
Case "Thursday"
Cells(r, "E").Value = 400
Case "Friday"
Cells(r, "E").Value = 500
Case "Saturday"
Cells(r, "E").Value = 600
Case "Sunday"
Cells(r, "E").Value = 700
Case Else
Exit Sub
End Select
End If
End Sub

Ken(not an expert!)
 
Back
Top