Drop down list IF, VLOOKUP Statement

Falko26

Board Regular
Joined
Oct 13, 2021
Messages
99
Office Version
  1. 365
Platform
  1. Windows
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?

1677511362207.png
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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.
VBA Code:
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.

1677527254147.png

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

1677527556390.png


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!
 
Upvote 0
Make sure you place this macro in the worksheet code module. It will work if you enter a value in the range C6:C10.
VBA Code:
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
 
Upvote 0
Solution

Forum statistics

Threads
1,224,817
Messages
6,181,149
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top