Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Slight problem.

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ok, I can't get my head around this problem that I have.

    Here goes.

    I have a few worksheets within the same workbook. One of the sheets has a list of employee names, addresses, contract etc. The other has the wage slips. I have two sheets for the wage slips, one for full time and the other for part time.

    I have managed to name the data within the employee records so its called "employee data". I have then used a validation list to lookup this data. Now its displays the surname of the employee. Once the surname is displayed, I have lookups to get the first name, address, pay etc.

    However I have two problems. The first problem is that the validation list shows all of the surnames, part and full time. Is there anyway of only showing one, so you can't select the wrong type?

    The second problem is that some people have the same surname, and a validation list will only recognise the first one of the two. How can this work?

    I'm not sure if anyone can help but this is out of my league. Any help would be much appreciated.


    If I haven't explained properly I can send the workbook over. For a copy of the workbook just reply.


    Thanks LOADS!

  2. #2
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    anyone?


  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    Can you use a unique identifier such as employee number, social security, etc. If so, you could create a list that looks up whether that employee number is full or part time. Based on this you could use the indirect function as your validation key with lists named "Part-time", "Full-Time".

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    that sounds like an Idea, but it also sounds confusing. What do I need to do to get that to work code wise.

    Thanks for the help

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I was thinking along the lines of formulas, I can't help with code. But the heart of the problems sounds like you don't have unique identifies.

  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hehe, sorry. Thats what I mean by code.

    I'm not sure what to do for the forumla and what these keys are in excel (know of them in access)

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I don't fully understand, but here is what I would propose

    To get around the multiple surname, add a unique identifer such as your companies employee coded to the left hand column of you r name worksheet. Instead of having them choose surname, choose the employee name. You can do this by naming your list and using this as the source in your valdiation. Now simply key your vlookup off the employee number (instead of the surname)
    =vlookup(employee#, your name worksheet, column, 0)

    To restrict only parttime users on the partime sheet and vice versa, add a full or parttime column on your name sheet. Sort by this and name part time employee numbers, for example "P_time". On the parttime time sheet where they are to select their employee number, use valdiation. Select list and type in =P_time in the source box.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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