0

enter image description here

I have a drop down list on Column B showing different categories (Sales, Sales-Adhoc, Repair, Maintenance & Others). When the user select Category, I would like the Reference No to reflect the type of Category selected.

For example, if user select Sales under Category, Sales will be represented by S and be shown as QTN-S-1501, instead of QTN-Sales-1501

P.S. The table on the right showing Category and Code is just a legend, not part of the table log.

Improve this question

1 Answer 1

Reset to default
0

Seeing that the first letter of your category is equal to the code you want to display you can simply use the =LEFT() function.

If I assume that your numbers are sequential in column A then cell A9 would be: ="QTN-"&left(B9,1)&right(A8,4)+1 which should yield QTN-S1506 if Sales was selected in B9.

You can also lookup the code from the table/legend you have on the right.

Improve this answer
3
  • Thanks Viridian, this is what I need. Just a question, what if one of the category under Others, I decided to use OT instead of O, can it still be done using LEFT()? It would have to be either single alphabet or double alphabet through out the list right? Correct me if I am wrong.
    – Brandon
    Sep 15, 2015 at 0:56
  • Brandon - That would be the easy way yes, but as always there is a workaround. Like I said, you can always 'lookup' what code should be returned, or use an 'if' if there is only one exception to the rule. ="QTN-"&if(left(B9,1)="O","OT",left(b9,1))&right(A8,4)+1 or the more universal ="QTN-"&vlookup(B9,$D$3:$E$7,2,FALSE)&right(A8,4)+1
    – Viridian
    Sep 16, 2015 at 12:23
  • Thanks Viridian, your formula works. Thanks for your kind help.
    – Brandon
    Sep 17, 2015 at 1:25

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .