+ Reply to Thread
Results 1 to 7 of 7

Populate Multiple Fields from Mutiple Column Listbox

  1. #1
    Registered User
    Join Date
    07-28-2009
    Location
    Grand Haven, MI USA
    MS-Off Ver
    MS Office 2007 Standard Edition
    Posts
    17

    Question Populate Multiple Fields from Mutiple Column Listbox

    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.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    02-03-2011
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Populate Multiple Fields from Mutiple Column Listbox

    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?

  3. #3
    Registered User
    Join Date
    07-28-2009
    Location
    Grand Haven, MI USA
    MS-Off Ver
    MS Office 2007 Standard Edition
    Posts
    17

    Re: Populate Multiple Fields from Mutiple Column Listbox

    Quote Originally Posted by rnsoltis View Post
    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!

  4. #4
    Registered User
    Join Date
    02-03-2011
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Populate Multiple Fields from Mutiple Column Listbox

    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.

  5. #5
    Registered User
    Join Date
    02-03-2011
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Populate Multiple Fields from Mutiple Column Listbox

    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

  6. #6
    Registered User
    Join Date
    07-28-2009
    Location
    Grand Haven, MI USA
    MS-Off Ver
    MS Office 2007 Standard Edition
    Posts
    17

    Re: Populate Multiple Fields from Mutiple Column Listbox

    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.

  7. #7
    Registered User
    Join Date
    07-28-2009
    Location
    Grand Haven, MI USA
    MS-Off Ver
    MS Office 2007 Standard Edition
    Posts
    17

    Thumbs up [SOLVED] Populate Multiple Fields from Mutiple Column Listbox

    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.
    Last edited by sgwilliams; 02-07-2011 at 02:50 PM. Reason: Trying to find out how to mark this issue "SOLVED"!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1