Results 1 to 3 of 3

Thread: Drop down list

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

    Drop down list

    I've been asked to create a sort of time sheet with start time, end time, total, and rate of pay. For the rate of pay, I've been asked if I could put a drop down list of four items. These are steward, supervisor, non steward, non supervisor. How do I create a drop down list containing these items, so they can be selected in each cell in the column Rate of Pay. I'm using Excel 2003.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi robm

    The simplest way for a few items in a validations list is this:

    Select the cell.
    From top-panel menu, select Data, then select Validation.
    In the Data Validation Settings panel, click the Allow dropdown and select List
    In the Source box, just enter your values separated by commas i.e enter steward, supervisor, non steward, non supervisor
    ..then click OK

    ..then copy this cell down to wherever you want it.

    all done.

    zeddy

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    robm,

    As zeddy has described, you can use drop down validation to create a dropdown selection list for cells. To make use of the list, here is some code the will automatically evaluate your selection in the Position column then place the employee's pay in the hourly rate column on the same row. Basic Excel formulas will enable you to complete the sheet from there.

    The code is placed in the sheet's module. If you need help doing that, post back.

    Alternately, you can use an Excel Formula placed in G2 then copied down:

    =IF(E2="steward",35,IF(E2="non steward",34,IF(E2="supervisor",40,IF(E2="non supervisor",34))))

    HTH,
    Maud

    Dropdown pay scale.png

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    'TEST IF CORRECT RANGE IS SELECTED (COLUMN E)
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Column = 5 And Target.Row > 1 Then
        Select Case Target.Value 'ASSIGN HOURLY PAY TO VARIABLE
            Case "steward"
                pay = 35
            Case "non steward"
                pay = 34
            Case "supervisor"
                pay = 40
            Case "non supervisor"
                pay = 34
        End Select
    End If
    Application.EnableEvents = False
    Target.Offset(0, 2).Value = pay 'INSERT HOURLY PAY IN COLUMN G
    '-----------------------------------------------
    'ADDITIONAL CODE CAN GO HERE
    
    
        
        
        
    '-----------------------------------------------
    Application.EnableEvents = True
    End Sub
    Attached Files Attached Files

Posting Permissions

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