Assigning numeric values to Text items in a dropdown list.

Question

Hello,

I am working on a personal project for my profession.

In Excel, I have text items in dropdown lists that I wish to assign numeric values. Each item will have an independent decimal value. What I am wanting to do is set it up so that when an item is selected from a list, it's numeric value (which I do not wish to be visible) can be applied to a math formula is several cells elsewhere in the project.

In the screenshot above, I have a lot of information (I'll clean it up later). However, what I want to do is in the dropdown menu under Film Class (upper left) I want each Class type to have its own numeric value that corresponds to the values in the table highlighted in yellow.

Also, if it is relevant, please be aware that I currently have two dropdown menus in the upper left. They are dynamic. Under Film Brand, when a different selection from the menu list is made, the list under Film Class changes accordingly. Not sure if it matters in this question, but I thought I'd include this detail in case it does.

I did look through the forum and found a few similar questions with answers, but I didn't get the results I wanted when I tired some of the solutions. So, I decided to post my question for my case specifically.

I may likely have future questions as I progress in this project. Thanks for any assistance!

-J Evans

Answer 1

Re: assign numbers to Data Validation text selection

If you can make each item unique in the DV list then this works...

(you use cell F4 to make your calculations)

'---

Nothing Left to Lose

https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU

(free excel programs)

Answer 2

Thanks for the assist, but it didn't work.

So, I rearranged my Film Class table (as you can see) thinking that if I were to put my associations in two single columns, it would be easier for Excel to understand what I am trying to do.

However, as you can see, the MATCH/XMATCH doesn't seem to be recognizing the menu C2 as a list it can work with. I think this may have something to do with the fact that it is a Dynamic dropdown menu. If I change the selection in menu B2 to a different brand (say, Fuji),

the list in menu C2 changes to a different list of items. And this IS what I want. I do want the Dynamic list, however, I want the items in menu C2 to associate with their corresponding numeric values, regardless of which List is present in the menu.

Am I making sense?

Thanks!

-J Evans

Answer 3

So.......

I got it!!!

I figured it out using a tutorial I found here.

Notice that when I make a selection from dropdown menu E3 that the cells in yellow have a calculation error.

This is because I need to make a subsequent selection for cell F3.

Now, the yellow cells populated with the desired numeric value for the Film Brand and Film Class shown in the table on the far left. In addition, the next thing I wanted it to do was apply that numeric value to an equation I am working on to the far right.

When I change the Film Class or Film Brand and Class, the value changes accordingly and is applied to the equation as well.

This is exactly what I was wanting.

Thanks for the help offered so far.

As I said, I will very likely have more questions, so I'll post on here again if I ever get stumped.

Thanks!

-J Evans

Answer 4

Re: "Thanks for the assist, but it didn't work."

Just for the record...

You used column 4 as the return column on a two column data set

and used xMatch instead of Match.

'---
Nothing Left to Lose