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. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 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

  3. #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

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 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

  5. #5
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,350
    Thanks
    48
    Thanked 273 Times in 251 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

  6. #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.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 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

  8. #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?

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 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 10:21.

  10. #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?

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    Yes, that is what I was indicating.

    Steve

  12. #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?

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 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
  •