Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Creating drop-down list

    I have a 'timesheet' spreadsheet which has a first name, and a Surname column. Can I create a drop down list to populate both cells from a named range of employees? I think selecting the surname from a drop down list, and have Excel populate the adjacent cell with the forename would be best.

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,129
    Thanks
    13
    Thanked 313 Times in 307 Posts
    Yes this can be done. Data-validation based on the named range or even a combo-box from the Form Controls set with the Input range of the named range would work.

    You can get the other information from the table with a lookup based on the selection.

    For a more detailed description I suggest attaching an example worksheet so we understand the setup.

    Steve

  4. #3
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I've attached the sheet. Basically, what I want to achieve is, pick the surname from a drop down list in Surname in column B, and have it put the First name in the adjacent cell, from the Employee table.
    Attached Files Attached Files

  5. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,129
    Thanks
    13
    Thanked 313 Times in 307 Posts
    Enter in A12:
    =INDEX('Employee List'!A:A,MATCH(B12,Employee,0))

    Copy it from A12 to A13:A311

    You can use a similar index for additional columns if desired... [If there a lot of lookups, I would add the intermediate calc of the MATCH to a new column and then reference that cell for the index, instead of having excel recalculate the match for multiple columns]

    Steve

  6. #5
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    998
    Thanks
    37
    Thanked 175 Times in 162 Posts
    Amending the formula to:

    =IF(B12="","",INDEX('Employee List'!A:A,MATCH(B12,Employee,0)))

    will eliminate the #N/A error if the surname is blank

  7. #6
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks guys, that's brilliant. Just one question now. Because the cells with formulas in produce data, it won't work when I protect them. Does this mean I can't protect them? I don't want the users to overwrite the data.

  8. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,129
    Thanks
    13
    Thanked 313 Times in 307 Posts
    Formulas will work when protected. The data-validation cells (which do not have formulas) will need to be unprotected since you want people to enter into these cells.

    Steve

  9. #8
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Okay, the goalposts have changed a little. Because quite a few of the employees have the same, or similar sounding surnames, they want to be able to select the first and surname from a drop down list, and then put it in the two seperate cells. Can this be done easily?

  10. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,129
    Thanks
    13
    Thanked 313 Times in 307 Posts
    Take your current list of first name and last name and combine them in a new column in the list. For example in C1 enter the formula:
    =B1&", "&A1
    For last comma First

    And copy this down the column. Then use this Column for the datavalidation list and lookup with the index to get the first and last name.

    Steve

    PS
    You way want to change the named formula you link to the data validation to a dynamic range, something like:
    =OFFSET('Employee List'!$C$1,0,0,COUNTA('Employee List'!$A:$A),1)

    So it will be as long as there are names in column A.
    Last edited by sdckapr; 2013-12-11 at 09:21.

  11. #10
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I'd have to have the drop down menu in another column then, wouldn't I?

  12. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,129
    Thanks
    13
    Thanked 313 Times in 307 Posts
    Yes, that is what I was indicating.

    Steve

  13. #12
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sdckapr View Post
    PS
    You way want to change the named formula you link to the data validation to a dynamic range, something like:
    =OFFSET('Employee List'!$C$1,0,0,COUNTA('Employee List'!$A:$A),1)

    So it will be as long as there are names in column A.
    Not sure about this bit. It doesn't seem to be working. What should it do?

  14. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,129
    Thanks
    13
    Thanked 313 Times in 307 Posts
    Could you elaborate? What have you tried and what exactly do you mean by "not working"? What are the symptoms? What error messages, what results, etc...

    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
  •