Value retrieval from the Multiple selection dropdown in excel

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

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. For e.g. I want if customer chooses category AAA & CCC, he/she should be able to see sum as 4. Also if customer first chooses all three category and then removes one of the category then also the sum should get updated. I am not getting how do i update my Vlookup formula to get the sum.
By: Ruts

Leave a Reply

Your email address will not be published. Required fields are marked *