Populate Multiple Fields from Mutiple Column Listbox

[sgwilliams]

Hello all i'm a novice at Excel, I have dabbled in VB and Access in the past with Access 2000. But that was a while ago and I'm really rusty. I have a small project that I'm working on for my Engineering Department. I'm trying to create a Thread Height and ChamferØ Calculator.

I attached what I have so far. The Yellow feilds are calculated fields and do not need to be changed. The Light Brown fields are the feilds that need to be populated from the table on sheet "ust" (Unified Screw Thread). The White feild is a hand typed field that we get from our catalog


The List Box needs to be populated with Multiple Columns from Sheet "ust" columns B2:B671 and D2:D671

I have tried to figure this out for the last few days but have run out of time and need to finish so I can get back to engineering. If someone would help me I would be in your debt, maybe I can engineer something for you for your time. Any help is greatly appreciated.

PS I will try and tackle the Metric tab after I get the Inch working which is the majority of my work.

[rnsoltis]

Just To be clear you are looking for all of the values in Column B and all of the values in Column D to be in one list?
Is there a reaseon they can't be two seperate that the user can select from?

[sgwilliams]

Just To be clear you are looking for all of the values in Column B and all of the values in Column D to be in one list?
Is there a reaseon they can't be two seperate that the user can select from?

I would prefer if all column B & column D values was in a list box. If it will make it easier I don't care if it is two list box or two combo box, just so I can select the Thread Designation and then the class and get the corresponding thread data to populate in the light brown fields. I'm very open to any suggestions as long as the end result is the same.

The data in the major, pitch and minor fields is a military specification and have to be followed so I prefer if they showed together, this will keep the end user from complaining about this, which I know they will if the list doesn't match the spec they are used to following.

Thanks!

[rnsoltis]

Fair Enough--- Here is what you need to do (there may be better/easier ways but this works)
Step 1 Create a combo Column on the UST chet Chich will create the list options.
1)a) I used column K for this and the fumula looked like this ="TD:"&B2&" Class:"&D2
Step 2 Make this a names range
2)a) Select K2:K671 To the left of the Equation line you will see the cell name (K2 in the example)
Type int the name you will use with no spaces. I used USTList_Options
Step3 create your list box
3)a) on the Inch tab select D14 and set a datavalidation of list the scource will be =name of the range (mine was =USTList_Options)
4 Use offset and match to lookup the values you need in each cell for Major MAX it was =OFFSET(ust!$A$1,MATCH(Inch!$D$14,ust!$K:$K,0),5)
the last number specifies the column you need to retrieve.

[rnsoltis]

sorry i forgot to make an adjustment the Brown fields formula would be =OFFSET(ust!$A$1,MATCH(Inch!$D$14,ust!$K:$K,0)-1,5)
The -1 adjust for the starting point of $A$1

[sgwilliams]

The Light Brown feild MajorØ -Max comes back with "FALSE", not sure what to do now . The drop down list box looks great. Now all I need are for the Brown Feilds to populate. I really appreciate your help so far you have been awesome thank you.

[sgwilliams]

Never Mind it was a glitch with that one field I messed with it for 15 minutes before I decide to delete the contents and start over, once I did that and repasted your formula it worked like a charm. I understand how it works now it's really quite genious. Thank you for your help. Your time is greatly Appreciated!

If you need anything engineered let me know and I'll get it modeled up for you anytime.