How to assign hidden values to specific text in my dropdown data validation that is later sum.
I have been trying to figure out a solution to the above where I can create a work schedule that when I add a work shift code, for example S1, then it would be representing 8 hours of scheduled work hours and S2 to represent 4 hours of scheduled work then when I total the 2 cells then I'm suppose to get 12 hours.
I know how to create the drop down, just don't know how to assign the hours to each work shift so when I'm summing those cells it is read as numbers even though text is visible.
Create a small lookup table. In the screenshot below, it is in E2.:F3.
E2:E3 can serve as source list for data validation.
Let's say you have entered Shift Codes in B2:B6.
The total number of hours corresponding to these codes is
Thanks for your response Hans.
Your solution should work if only I know how to do shift codes. Please explain further.