Drop Down Lists - Values assigned to Text

Hi everyone,

I’m a bit stuck on where to put this query as its within the excel worksheet, however if possible, I’d quite like it to work using VBA (if possible) within the worksheet. Although i’m not sure if it can be done.

I want to create 4 separate lists on the same worksheet. Within the lists, I’d like each option (which will be text of some kind) to have a figure assigned to it.

For example:

Blue = 1
Apple = 2

What I’d like is when selecting “Blue” from the drop down box, it displays as “Blue” in the sheet but is actually seen by the cell as having a value of 1. Then I’d like each value to be calculated (added together) to create a sum value.

Let me give you an example.

Lets say I have 4 Lists (In Cells A1, B1, C1, D1):

List 1
Blue = 1
Red = 2
Green = 3

List2
Orange = 1
Apple = 2
Mango = 3

List3
Carrot = 1
Onion = 2
Garlic = 3

List 4
Car = 1
Van = 2
Bike = 3


Lets say in each list I have the following options selected.

Blue / Apple / Carrot / Bike

In Cell E1 I’d like the value = 7 to be displayed.

Quite important is that not all the cells have to have an entry for the sum to work. So lets say I have no value in list2 selected. I’d like:

Blue / Carrot / Bike

In Cell E1 I’d like the value = 5 to be displayed.

I’m not sure if this can be done within the sheet itself, or if its easier doing so via VBA and then linking to lists within the sheet. I think VBA would be a much simpler way and cleaner way of doing it (if its possible.)

I hope I make sense with what I’m saying though. I really hope someone can help.

Thank You,

Hi Fotis,

Thank you for the suggestion.

That works fine for one individual list. However I have 4 seperate lists. Can this be edited to use 4 seperate lists?

Hi Fotis,

I'm really sorry, I was getting a little bit confused (long day at work.) As you say, your original suggestion works perfectly. It's exactly what i'm looking for.

Really really appreciate your help! Thank you very much.

Hello,

Sorry to bring this up. But my issue doesn't seem to be resolved. The workings are there however the addition doesn't seem to be working correctly.

With the following code. If I was to select an option in List 1 that has a value of 1.8, and an option in List 2 that have a value of 1.8. The result is 5.4 , rather than 3.6 as it should be.

Please Login or Register  to view this content.

Can anyone see why that would be the case?