Forum Discussion
kirkBolton
Nov 22, 2020Copper Contributor
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...
- Dec 01, 2020
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)))
HansVogelaar
Nov 22, 2020MVP
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))
 
- kirkBoltonNov 23, 2020Copper Contributorlol, 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.
- kirkBoltonNov 23, 2020Copper Contributor
Thanks for your response Hans.
Your solution should work if only I know how to do shift codes. Please explain further.
With thanks.
Kirk