How to assign numerical values from a dropdown list in Excel 2010

5 pts.
Tags:
Microsoft Excel 2010
Microsoft Excel macros
I'm trying to write a roster template, and need to be able to assign numerical values to shift types (AM/PM 9, MIDDBL 12, DBL 14 and OFF 0). Each week day currently has a drop box for each employee listing the bracketed shift options. Ultimately I want to be able to calculate how many hours are rostered per shift, as well as how many hours each staff member is working. Then extrapolate that info to wage costs per day etc. Trying to do this on 2010, any assistance much appreciated.
0

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Brijesh
    Hi,

    There is a list option in Data validation. If this is combined with a VLOOKUP formula you would be able to convert the selected value into a number.

    Steps in Excel 2010 are:

    • Create your list with matching values.
    • On the Data tab choose Data Validation
    • The Data validation form will be displayed
    • Set the Allow dropdown to List
    • Set the Source range to the first part of your list
    • Click on OK (User messages can be added if required)

    In a cell enter a formula like this

    =VLOOKUP(A2,$D$3:$E$5,2,FALSE)
    

    which will return the matching value from the second part of your list.

    Screenshot of Data validation list

    Controls can be placed on a worksheet. They can be linked to a range and return the position number of the selected value to a specific cell.

    The steps in Excel 2010 are:

    • Create your list of data in a worksheet
    • Click on the Developer tab and dropdown on the Insert option
    • In the Form section choose Combo box or List box
    • Use the mouse to draw the box on the worksheet
    • Right click on the box and select Format control
    • The Format control form will be displayed
    • Click on the Control tab
    • Set the Input range to your list of data
    • Set the Cell link range to the cell where you want the number of the selected item to appear
    • Click on OK

    Screenshot of form control

    Hope this helps.


    14,450 pointsBadges:
    report
  • Tervion
    please give more details

    20 pointsBadges:
    report
  • Subhendu Sen

    Please link here for Microsoft help, though it is based on Excel 2013/ 16 but concept is almost same, https://support.microsoft.com/en-in/help/4027045/excel-create-a-drop-down-list-in-excel

    Also there is a very good help in Excel. Click Help menu and you can get numerous examples.

    134,420 pointsBadges:
    report
  • Tervion
    I mean how did you link the values to the items ? When I have to crate the data validation list I will select the items in the list (example d3, d4, d5) but then what? How do I tell the validation list that the next column E is the location of the values that are coresponding to those items? Also in the second example I dont see any separete list of values yet they magicly apear in column B. Finaly is there a way you can embed that function for the entire coulmn? VBA maybe if yes can you show me a sample code. 
    20 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: