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

[kirkBolton]

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

[#2]

@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))

 

[#3]

@HansVogelaar 

Thanks for your response Hans.

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

With thanks.

Kirk

[#4]

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.

[#5]

Greetings Hans, thanks again for the solution. I'm now realizing that some of the assigned cells are not giving the results. So some works and some doesn't. Do you know what might be causing this. Maybe I can send you the form to look at. I'm going to send you the file. The results are not posting for some of my staff codes

[#6]

@kirkBolton 

Your schedule is completely empty, so it's no wonder that there is no result.

But the formula that I provided assumed that all days have been filled in. The following version (again confirmed with Ctrl+Shift+Enter) handles blanks too:

=SUMPRODUCT(IFERROR(LOOKUP(E6:K6,$AD$4:$AD$17,$AE$4:$AE$17),0))

This can be filled down.

[#7]

Hi Hans, thanks for your feedback. I accidentally sent you a blank form. Please find attached a copy of the form using both the initial formula you provided and your most recent formula. Your most recent formula is used in the yellow highlighted cells. I am not getting the results of the shift code value using the new formula you provided. In blue highlight is the use of your initial formula but it's not giving the correct corresponding value for the shift code.  I would greatly appreciate your assistance in resolving this issue. I can't understand why the values for the corresponding shift code is giving a different result. 

THANKS AGAIN.

[#8]

@kirkBolton 

When LOOKUP is used to return multiple results, the lookup_vector (or array) argument must be sorted in ascending order. Thus, the formula in K5 in the attached version of your file is:

=SUMPRODUCT(LOOKUP(D5:J5,$AB$4:$AC$22))

[#9]

@Twifoo 

Thanks for your input, Robert. The suggestion still didn't fix my problem. Is there any other option you can suggest.

[#10 · Accepted solution]

@kirkBolton I'm not sure why that isn't working for you because when I opened it the numbers appear correctly.  Please note that what @Twifoo did was rearrange your lookup table to be in alpha-numerically increasing order (e.g. S1 then S10, ..., S14, then S2  because '1' is before '2' so 'S1...' is before 'S2...').  That said, here are 2 alternative suggestions you could try:

If you have XLOOKUP() I suggest using it:

=SUMPRODUCT(XLOOKUP(D6:J6,$AB$4:$AB$22,$AC$4:$AC$22,0,0))

 You could also use INDEX-MATCH combination to force an exact match (I added the IFERROR just in case but since you have data validation you could remove that)

=SUMPRODUCT(INDEX($AC$4:$AC$22,IFERROR(MATCH(D7:J7,$AB$4:$AB$22,0),1)))

[#11]

@mtarler 

Your solution using =SUMPRODUCT(XLOOKUP(D28:J28,$AB$4:$AB$22,$AC$4:$AC$22,0,0)) worked perfect. 

Thank you and thanks to everyone. I have learnt so much from the community. I am so grateful for this resource and the skills of you guys. Thanks

[#12]

@mtarler 

Hi, I have a similar problem. 

Firstly I am a total novice at excel. I'm trying to assign the values to the cells and have followed your formula, however I do not know how to assign the code to the target cell. 

In the Table I want LS to have the hidden value of 4 but I do not know how to assign it. 

Any suggestions?

Thanks in advance

[#13]

Hi Shell78 and welcome to the Excel forum.
a) I'm not clear what you want/need. you say you want LS to have the value of 4, is that it or are there more items? In the original post here there was a whole list of items (S1, S2,...) each with there own value.
b) It is best if you start a new thread for your specific issue. Old resolved threads are for looking up previous answers which can be very helpful, but it gets confusing and cluttered if we tack on additional issues to the same thread.
c) I suggest you attach or give a link to your sheet or a de-identified/unclassified version of it or at the very least copy a table into your post with your example/set-up/situation. The more information you give us the better chance we can help you.

[#14]

@mtarler Thank you for your reply, 

I will start another question.

but to answer your question. I want to assign shifts to my employees but I don't want the hours visible. So they will be assigned as below. 

I would like the shift codes to have an underlying value and add up to the amount of daily hours in the total box, then be carried over to another sheet with the weeks hours.

Does this make sense?

Rachelle

Employee shift schedule1.xlsx