Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Feb 2004
    Location
    Franktown, Colorado, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Autofill a cell after selecting from combo box (2003)

    I am trying to figure out how to auto-populate a cell based on a selection made in another cell's combo box. On the worksheet (but out of view of the user), I have a 2-column list of facility-names with their corresponding facility-codes. In the main worksheet, cell A1 contains a combo box that uses a data validation list pointing to the first of the 2-column facility list. When a user selects a facility-name value from the list, I want to auto-populate cell A2 with the facility-code that relates to the selection in A1. I can't for the life of me figure out an easy way to accomplish this. There are about 22 facilities in the list and I want to limit the user's selection to the list. Any insight will be greatly appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Autofill a cell after selecting from combo box (2003)

    Enter this formula in cell A2:

    =VLOOKUP(A1,list,2,FALSE)

    where list is either the address of the 2-column list, or its name if you have named the range.

  3. #3
    New Lounger
    Join Date
    Feb 2004
    Location
    Franktown, Colorado, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofill a cell after selecting from combo box (2003)

    Thanks, Hans! It works wonderfully.

    BJSmithCO

  4. #4
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofill a cell after selecting from combo box (2003)

    If you name your two lists say FacNames and FacCodes
    then enter in cell A2:

    =INDEX(FacCodes,MATCH(A1,FacNames,0))

    you will find that (unlike the VLookup function), the Facility Names no longer need to be in alphabetical order and it will still work if there is a blank cell in your list.

    HTH

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Autofill a cell after selecting from combo box (2003)

    Setting the 4th argument of VLOOKUP to FALSE, as in the formula I proposed, hase the same effect as setting the 3rd argument of MATCH to 0: Excel looks for an exact match instead of the nearest one, and therefore the list does not need to be sorted.

  6. #6
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofill a cell after selecting from combo box (2003)

    Thanks Hans for the new info.

    Does Vlookup also work with blanks in the Lookup list?
    I have also found that INDEX/MATCH will find a result from a column that is to the LEFT of the lookup column.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Autofill a cell after selecting from combo box (2003)

    VLOOKUP does work with blanks in the list. It can NOT be used to return values to the left of the lookup column, you need INDEX and MATCH for that (also see <!post=this star post,244408>this star post<!/post>).

  8. #8
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofill a cell after selecting from combo box (2003)

    What happens if the facilty list has 2 names the same? I have tried to use this formula on a staff list. If 2 people have the same last name the vlookup will only return the first name from the list. From what I have read this is a draw back to using vlookup. The same happens with an Index/Match. Other than assigning some unique number code to each person, is there another way to handle duplicate names from the data validation list?

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofill a cell after selecting from combo box (2003)

    The typical way of handling this problem is to add another column (possibly hidden) to the lookup table that concatenates the first and last names (and middle initial if it is available), and then use that column for the lookup.
    Legare Coleman

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Autofill a cell after selecting from combo box (2003)

    <P ID="edit" class=small>(Edited by sdckapr on 07-Jan-05 16:03. Fixed link)</P>I don't understand. If there are duplicate names in the validation list, the cell would contain (eg) "Smith" or it would contain "Smith". Given the name "Smith" how would you knowing which "Smith" was selected? Datavalidation only places the selection, you have no idea what location it was from.

    You could use combobox from the forms or control toolbox to get the index value and then use that value in the index.

    If you want datavalidation, why not add the first name to the list and lookup on the combined first/last name?

    If you want to "lookup" more than 1 row see <post#=395235>post 395235</post#>, though I don't think this is what you are after.

    Steve
    Steve

Posting Permissions

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