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 use VLOOKUP(E2;Sheet2!$I$2:$J$5;2;0). $I$2:$J$5 instead of your I2: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 in E2, E3, etc. If you omit the 0, your results are no longer reliable, because then VLOOKUP may do approximations which you will not anticipate. – ZygD Mar 28 '19 at 12:35
0
=SUM(IF(ISERR(FIND(Sheet2!$I$2:$I$5;A1;1));0;Sheet2!$J$2:$J$5))

This must work, but it is not a regular formula. It is an array formula. For array formulas to work, you enter it not with Enter, but instead use a combination of Ctrl+Shift+Enter.

Also, change the A1 to your actual dropdown cell.

Example

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.