Forum Discussion
How to assign hidden values to specific text in my dropdown data validation that is later sum
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)))
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
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
- 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.
