I'm making a very simple excel spreadsheet for use in the machining industry, and it uses a drop-down list to offer different metal alloys commonly used in machining.



I want the drop-down list to offer a list of metals expressed in English words, number codes, or some combination. For example, "12L14" and "1144" are both commonly-known designations for steel alloys...or..."360" is a designation for a brass alloy. At any rate, I need the drop-down list to offer the choices using terms a machinist will understand.

My problem is that I can't figure out how to make a number associate itself with the different metal alloy designations - the numbers are used in a specific math formula for calculating the needed speed to set a milling machine or lathe to cut that particular metal. Each alloy has a certain numerical value called "surface feet per minute" that allows it to be machined well, so each metal alloy has a different number.

Some examples of the alloy designations with their SFPM numbers would be:
1144 = 50
Drill Rod = 50
12L14 = 80
Brass = 150
Aluminum = 250
Etc., etc., etc.

So how can I offer a drop-down list that offers metal alloy choices while elsewhere in the workbook a formula inserts a number for calculation that is unique to that metal alloy? I can write the formula with no problem, I just don't see how I can make the formula pick up the specific number I need based on the metal alloy the user chooses from my drop-down list.

Thanks in advance.

Before I get into the how (if you need me to), see if the attached works to your liking.

Attached Files

MaterialSFPM.xlsx (11.0 KB, 10 views)

Thanks for that. I think I see where you're going with that, but I would like the SFPM number to not appear in the 1st tab where the operator enters his variables. There are only two variables: metal alloy designation, and the diameter of the proposed cutting tool.

So...although I appreciate your input, I still need a way to associate a number with a given metal alloy designation.

Thanks...

Let me see if I got this right. You want to enter a particular alloy grade (e.g. 12L14, 1018, etc.) and then calculate some equations using the value for SFPM that it looks up, right?

Yes, exactly. I think you've got it! I want a list of metal alloys to be selectable from a drop-down list. That way my end users will merely choose their metal alloy by the names that they are familiar with. But since a formula can't know what the SFPM number for that alloy is, I would like (if possible) for a number to be somehow automatically associated with the particular alloy the user chooses. So that way, the user's alloy choice will "trigger" the correct SFPM number to be used in the formula for RPM calculation.

See attached. The user selects the alloy and enters the stock diameter. The formula for the RPM then is (I think):

=IFERROR(12*VLOOKUP(A2,MaterialSFPM,2,FALSE)/(PI()*C2),"")

Since the vlookup to find the SFPM is included in the formula, the SFPM column is superfluous and can be deleted.

For 1/4" stock brass, the formula returns 2292. If this is overly precise and you would like it rounded to the closest hundred (included as RPM rounded):

=IFERROR(ROUNDUP(12*VLOOKUP(A2,MaterialSFPM,2,FALS E)/(PI()*C2),-2),"")

For the closest, ten, change that -2 near the end to -1. Easy peasy. :-) It would probably be a good idea, once you have it all fleshed out, to lock any formula cells and protect the sheet. If you need any further help with that or anything else, let me know.

Attached Files

MaterialSFPM.xlsx (13.2 KB, 9 views)

By jove, I think you've got it! You seem to have used a formula that I've never tried...the "IFERROR" formula. I think I can take that and run with it, but I might be back soon with another question or two.

Thank you!

Glad I could help. The iferror in this case only accounts for if the alloy cell is blank, in which case the vlookup would return an error otherwise.