0
0
I am trying to make calculation from my dropdown menu. I have following dropdown in sheet 1 in my Excel.
## Category ##
### AAA ###
### BBB ###
### CCC ###
### DDD ###
In sheet 2, I have corresponding values for this dropdown.
## Category Category Value##
### AAA 1###
### BBB 2###
### CCC 3###
### DDD 4###
I added VBA code for multiple selection and also added simple VLOOKUP
formula to retrieve the value of category.
=VLOOKUP(E2;Sheet2!I2:J5;2;)
With the VBA code, I am able to select all three category and also remove the selected category later. But I am failing to retrieve the sum of selected category. E.g., if a customer chooses category AAA & CCC, he/she should be able to see sum as 4. Also, if a customer first chooses all three categories and then removes one of them, then the sum should get updated. I am not getting how do I update my VLOOKUP
formula to get the sum.
Here is my VBA code for multiple selection.
Private Sub Worksheet_Change(ByVal Target As Range)
'Updated: 2016/4/12
Dim xRng As Range
Dim xValue1 As String
Dim xValue2 As String
If Target.Count > 1 Then Exit Sub
On Error Resume Next
Set xRng = Cells.SpecialCells(xlCellTypeAllValidation)
If xRng Is Nothing Then Exit Sub
Application.EnableEvents = False
If Not Application.Intersect(Target, xRng) Is Nothing Then
xValue2 = Target.Value
Application.Undo
xValue1 = Target.Value
Target.Value = xValue2
If xValue1 <> "" Then
If xValue2 <> "" Then
' If xValue1 = xValue2 Or _
' InStr(1, xValue1, ", " & xValue2) Or _
InStr(1, xValue1, xValue2 & ",") Then
If InStr(1, xValue1, xValue2 & ",") > 0 Then
xValue1 = Replace(xValue1, xValue2 & ", ", "") ' If it's in the middle with comma
Target.Value = xValue1
GoTo jumpOut
End If
If InStr(1, xValue1, ", " & xValue2) > 0 Then
xValue1 = Replace(xValue1, ", " & xValue2, "") ' If it's at the end with a comma in front of it
Target.Value = xValue1
GoTo jumpOut
End If
If xValue1 = xValue2 Then ' If it is the only item in string
xValue1 = ""
Target.Value = xValue1
GoTo jumpOut
End If
Target.Value = xValue1 & ", " & xValue2
End If
jumpOut:
End If
End If
Application.EnableEvents = True
End Sub
First, your
VLOOKUP
is messed up. You should useVLOOKUP(E2;Sheet2!$I$2:$J$5;2;0)
.$I$2:$J$5
instead of yourI2:J5
, so that when you copy the formula down, your lookup range does not go down too.0
at the end is necessary for finding exactly what is inE2
,E3
, etc. If you omit the0
, your results are no longer reliable, because thenVLOOKUP
may do approximations which you will not anticipate. – ZygD – 2019-03-28T12:35:43.950