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.
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.
Discuss This Question: 4  Replies
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:
In a cell enter a formula like this
which will return the matching value from the second part of your 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:
Hope this helps.
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.