Drop down list IF, VLOOKUP Statement

[Falko26]

Hey Team,

Is it possible to have an IF statement imbedded in your drop down list that automatically fills in your selection depending on a cell value??

In the below example I have a drop down list for my "TYPE". Id like to add an IF statement to my drop down list that would work as follows. =IF(C15>0, VLOOKUP(C15,$C$20:$D$24,2),Drop Down List)

So if C15 (Item #) is grater than "0" I want it to automatically fill in "TYPE" with the value associated with the item number, in this case "LABOR". If C15 (Item #) is 0 or has no value added I want the drop down list to function as normal.

Is something like this possible?

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in E15.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address(0, 0) <> "E15" Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Range("C15") > 0 Then
        Dim Item As Range
        Set Item = Range("C20:C24").Find(Range("C15").Value, LookIn:=xlValues, lookat:=xlWhole)
        If Not Item Is Nothing Then
            Target = Item.Offset(, 1)
        End If
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[Falko26]

Hey Mumps,

Thanks for the reply. I could not quite get this to work on my end. I pasted your code, changed the value in C15 and nothing happened. E15 was still operating as a drop down list. I also deleted the drop down list from E15 and tried with no luck.

If I'm understanding this right. changing the value in C15 should activate the VBA code which looks in the range C20-C24 for the value input into C15 then returns the value one row to the right of the number value into E15. which all makes sense just didn't seem to function properly?

Simply put:
I'm trying to keep the drop down list intact while also being able to overwrite it with a formula whenever I see fit.

If C15<0 use drop down list in E15.
If C15>0 then use a formula in E15 of my choosing.

 

[mumps]

The macro assumes that the number in C15 exists and the macro runs when you make a selection in E15. If this is not what you want, please explain step by step what you want to happen.

 

[Falko26]

Thanks for your time ill re explain step by step.

Normal Operation
- Value in C6 = 0
- There for Cell E6 functions as a drop down box so you can select whatever you want.

Secondary Operation:
- You input the Value 10 into Cell C6
- Cell E6 now automatically fills in the value "LABOR" based off the table below.

Based on the value input into C6. E6 automatically populates without having to use the drop down list.

What I may end up doing is simply removing the drop down functionality all together and just have the employee use the Item Number with a VLOOKUP to populate E6. I was just trying to preserve the functionality of the dropdown list while also allowing the use of the item code to populate E6.

For this example the drop downs would be fine since its only 6 items but I have other examples in this spreadsheet that are 100 items long so it would be easier to type a number to populate E6 instead of looking at a dropdown list 100 items long.

Thanks!

 

[mumps]

Make sure you place this macro in the worksheet code module. It will work if you enter a value in the range C6:C10.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("C6:C10")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    If Target > 0 Then
        Dim Item As Range
        Set Item = Range("B15:B20").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
        If Not Item Is Nothing Then
            Target.Offset(, 2) = Item.Offset(, 1)
        End If
    End If
    Application.ScreenUpdating = True
End Sub

 

[Falko26]

Thanks for your Help! Works great!

 

[mumps]

You are very welcome.