Excel Check Vlookup value if cell value matches

Jyotirmaya

Board Regular
Joined
Dec 2, 2015
Messages
168
I have two Sheets "DATA" "RAW DATA"

In DATA sheets

Column A COLUMN B
APPLE 10
APPLE 15
APPLE 16.5
BANANA 35
BANANA 20
BANANA 36
ORANGE 45
ORANGE 15
ORANGE 50

In RAW DATA sheet in Column B I have to input APPLE/BANANA/ORANGE in various rows.

I want that if in B2 I have entered APPLE then in C2 I can enter only 10 or 15 or 16.5 if anything else I will enter then I will get error message that enter correct value. Similarly If I will enter Banana in column B of Raw Data sheet then I can only input values 35 or 20 or 36 in the column C

SO what will be the code for Column C of Raw Data sheet to do so ?
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Takae

Well-known Member
Joined
Jul 1, 2015
Messages
720
Copy to sheet module of RAW DATA sheet.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Dic, i As Long, LR As Long
Dim list As String
Dim Dws As Worksheet
    If Intersect(Target, Range("B:B")) Is Nothing Then
        Exit Sub
    Else
        Set Dws = Sheets("DATA")
        Set Dic = CreateObject("scripting.Dictionary")
    
        On Error Resume Next
        With Dws
            LR = .cells(Rows.Count, 1).End(xlUp).row
            For i = 1 To LR
                If .cells(i, 1).Value = Target.Value Then
                    Dic.Add .cells(i, 2).Value, .cells(i, 2).Value
                End If
            Next
        End With
    
        If Dic.Count = 0 Then
            MsgBox Target.Value & " does not exist in DATA sheet."
            Target.Offset(0, 1).Validation.Delete
            Exit Sub
        End If
        
        list = Join(Dic.keys, ",")
        
        With Target.Offset(0, 1).Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=list
            .IgnoreBlank = True
            .InCellDropdown = True
            .ShowInput = True
            .ShowError = True
        End With
        Set Dic = Nothing
    End If
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,715
Messages
5,597,726
Members
414,169
Latest member
Preston_Cleric

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
Top