Forum Discussion
Solved
How to assign hidden values to specific text in my dropdown data validation that is later sum
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 wh...
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)))
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.
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))Thanks for your input, Robert. The suggestion still didn't fix my problem. Is there any other option you can suggest.
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)))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
