Associating strings in pull-down menu to variables

[billsmith]

I'd like to know how to program cells so that the user selects a string or variable from a pull-down list, and that string or variable is associated with an integer.
For example, maybe I want to figure the cost of making different items of clothing. I might have a cell with 3 options that I want represented by a number:

• Shirt = 1
• Pants = 2
• dress = 3

These numbers might represent the level of difficulty to produce the item. Then the next cell might represent the fabrics:

• cotton = 1
• wool = 2
• leather = 3.

These numbers might represent the level of difficulty in working with the fabric.
Then I might add the total and multiply the total by some baseline figure (say $100) to get the cost of production.
I might have more factors, but if someone can tell me how to program these, I assume I can figure out the others. I've reviewed some of the Chandoo materials, including some VBA examples, but haven't figured this out.
Thanks!
Bill

 

[PCosta87]

Hi,

Something like this (please see attachment)

If you have any questions about how to make the drop-downs feel free to ask.

hope this helps

 

[billsmith]

Hi,

Something like this (please see attachment)

If you have any questions about how to make the drop-downs feel free to ask.

hope this helps

Thanks very much! This was very helpful, works fine. If you have time, please explain the formula, where the formula is coded, and also, how to make pulldowns (I've done that, but don't remember).
Also, where would I learn more about how to do this? Would I need a course in VBA?

 

[PCosta87]

Thanks very much! This was very helpful, works fine. If you have time, please explain the formula, where the formula is coded, and also, how to make pulldowns (I've done that, but don't remember).
Also, where would I learn more about how to do this? Would I need a course in VBA?

Hi,

Something like this should help you understand the basics about the drop-downs (data validation):
https://support.office.com/en-us/ar...to-cells-29fecbcc-d1b9-42c1-9d76-eff3ce5f7249

As for the VLOOKUP function please refer to:
https://support.office.com/en-us/ar...963-8ab8-93a18ad188a1?ui=en-US&rs=en-US&ad=US

No VBA needed here
Simply put, the validation allows you to create the pull-down menu to select from a list of values... as for the VLOOKUP formula, it will look for the value on a specified range and return the value in the "nth" column to the right (important to notice that the value you are looking up must appear on left most column of your selected range, so if you are looking for "Shirt" in columns "A:B" then "Shirt" must be in column A)

Hope this helps
If you have any further questions feel free to ask

 

[Justin Greenwood]

A different way of doing it. This way you can add items and fabric to the list add work load and base cost to each item.

These are simple Vlookups you can find that information HERE

As for a drop down list please visit HERE

Opps I missed out the data validation to allow new items bad me!