How to assign hidden values to specific text in my dropdown data validation that is later sum

%3CLINGO-SUB%20id%3D%22lingo-sub-1921527%22%20slang%3D%22en-US%22%3EHow%20to%20assign%20hidden%20values%20to%20specific%20text%20in%20my%20dropdown%20data%20validation%20that%20is%20later%20sum%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1921527%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20to%20assign%20hidden%20values%20to%20specific%20text%20in%20my%20dropdown%20data%20validation%20that%20is%20later%20sum.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20been%20trying%20to%20figure%20out%20a%20solution%20to%20the%20above%20where%20I%20can%20create%20a%20work%20schedule%20that%20when%20I%20add%20a%20work%20shift%20code%2C%20for%20example%20S1%2C%20then%20it%20would%20be%20representing%208%20hours%20of%20scheduled%20work%20hours%20and%20S2%20to%20represent%204%20hours%20of%20scheduled%20work%20then%20when%20I%20total%20the%202%20cells%20then%20I'm%20suppose%20to%20get%2012%20hours.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20how%20to%20create%20the%20drop%20down%2C%20just%20don't%20know%20how%20to%20assign%20the%20hours%20to%20each%20work%20shift%20so%20when%20I'm%20summing%20those%20cells%20it%20is%20read%20as%20numbers%20even%20though%20text%20is%20visible.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1921527%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1921568%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20assign%20hidden%20values%20to%20specific%20text%20in%20my%20dropdown%20data%20validation%20that%20is%20later%20sum%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1921568%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F877811%22%20target%3D%22_blank%22%3E%40kirkBolton%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECreate%20a%20small%20lookup%20table.%20In%20the%20screenshot%20below%2C%20it%20is%20in%20E2.%3AF3.%3C%2FP%3E%0A%3CP%3EE2%3AE3%20can%20serve%20as%20source%20list%20for%20data%20validation.%3C%2FP%3E%0A%3CP%3ELet's%20say%20you%20have%20entered%20Shift%20Codes%20in%20B2%3AB6.%3C%2FP%3E%0A%3CP%3EThe%20total%20number%20of%20hours%20corresponding%20to%20these%20codes%20is%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUMPRODUCT(LOOKUP(B2%3AB6%2C%24E%242%3A%24E%243%2C%24F%242%3A%24F%243))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0003.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F235448i60F99CC953428506%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22S0003.png%22%20alt%3D%22S0003.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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.

 

Thanks

3 Replies
Highlighted

@kirkBolton 

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

 

=SUMPRODUCT(LOOKUP(B2:B6,$E$2:$E$3,$F$2:$F$3))

 

S0003.png

Highlighted

@Hans Vogelaar 

 

Thanks for your response Hans.

Your solution should work if only I know how to do shift codes. Please explain further.

 

With thanks.

Kirk

Highlighted
lol, Apologies Han, just realized the shift codes are the same S1 etc that I referred to in the beginning. Scrap that earlier response let me try applying the formula u sent.