This Excel Factor tip was sent in by Marc Joannette of Montreal, Canada.
Words by Mynda Treacy.
Marc's job involves reissuing airline tickets. Part of this process requires a 'reason code' to be selected from a list. So he built a form where you can select a reason from a data validation list and the reason code (number) will be returned, like this:
And the good news is it doesn’t use any fancy formulas, just some simple custom number formats and a little Data Validation.
Step 1 Custom Number Format
First Marc set up a list of reason codes to feed the data validation list.
As you can see in the example below each cell contains the reason code description, but if you look in the formula bar you can see the value in the cell is actually the reason code number.
To do this Marc entered his reason codes in cells A1:A4, then set up separate custom number formats for each cell which displayed the reason description instead of the actual number value in the cell.
How to Set up a Custom Number Format
- To set up a custom number format select the cell you want to format then press CTRL+1 to open the Format Cells dialog box.
- On the Number tab select ‘Custom’ from the ‘Category’ list.
- In the ‘Type’ field Marc entered his custom number format, which is simply the reason description enclosed in double quotes (see image below).
In fact any number that is entered in a cell formatted with the above custom number format will display ‘Agent Error’.
A little about custom number formats:
Number formats can have up to 4 sections of code which are separated by semicolons. Each code section defines the format for positive numbers, negative numbers, zero values and text, in that order.
<POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>
In Marc’s custom number format he only has one section of code (“Agent Error”), which means that this number format will be applied for all sections of the code.
Now, Custom Number Formats is a topic all on its own, so rather than digress; here is a link where you can read Jon von der Heyden’s comprehensive guide to custom number formats. Click the link later though as I'm not finished explaining Marc's tip... 🙂
Step 2 Data Validation List
Set up the Data Validation list referring to the custom formatted cells A1:A4 (I’ve given them a named range ‘reason_codes’).
- Select the cells to which you want to apply the Data Validation List.
- On the Data tab of the ribbon select Data Validation
This will open the Data Validation dialog box below. In the settings tab select ‘List’ from the ‘Allow’ criteria and in the ‘Source’ enter your range or in my case, ‘named range’, and press OK.
Now when you select the cells with data validation you will be able to choose from the exception code descriptions and have the reason code number value returned:
The benefit of this approach is that you can use the underlying number value in other formulas that do not handle text, like SUM, AVERAGE, MIN, MAX etc.
Enter your email address below to download the sample workbook.
Thanks for sharing your tip, Marc.
A few words from Marc:
“I am not a programmer but I am a technical person in general (my family would call for help with simple computer installations and formatting, and at work I understand the software and "programmable keys" and I use them to the maximum), but up until last year, I only used Excel for my monthly budget.Last year, I wanted to do an airline reissue template to assist me in manual airline ticket reissues, (that is the work I do) ... those PFC taxes are calculated as an amount per departure city, up to four cities, then it's the first 2 cities and the last two cities, ... it was so hard to do that formula - the first 2 and last 2, or in my case about 16 formulas to get the result needed(version 1)...the final version, has a lot less.
The more I learnt, the more I caught on and enjoyed.
I have found a passion for finding a formula that will do what I want it to do."
Marc
Vote for Marc
If you’d like to vote for Marc's tip (in X-factor voting style) use the buttons below to Like this on Facebook, Tweet about it on Twitter, +1 it on Google, Share it on LinkedIn, or leave a comment to thank Marc for taking the time to suggest this tip….or all of the above 🙂
Excel Factor 19 Dynamic Dependent Data Validation
Phil Kowalski
Hey Mynda,
just saw this post from the other one about custom number formats. First I though that this does only work with numbers but I found a way around it.
I had a three letter shortcode which I would have loved to be replaced with its “long name”.
E. g. AAA should be replaced “My long name”
The article refers to replacing positive numbers but if you put three semicolons in front of it, the approach works as well for texts. See also Mynda’s post on custom number formats from 03/21/2014.
Mynda Treacy
Brilliant! Thanks for sharing, Phil.
Raymond
Hi Mynda,
As always, awesome little tricks 🙂
Muchos gracias.
Gr,
Raymond
Mynda Treacy
You’re most welcome, Raymond 🙂 Glad you liked them.
Turab
Can a XL sheet be used as a form on the VB? If so then can we use this concept for creating a Combo Box and selet item from the list of from that box.
Mynda Treacy
Hi Turab,
No, Combo Boxes return a reference number that represents the number of the item in the selected list. i.e. if you select the first item it returns a 1, the second item a 2 and so on.
However, the Combo Box will display the ‘Source Description’ as opposed to the underlying ‘Source Code’.
Kind regards,
Mynda.
Andrew Evans
There is an error in the spreadsheet – it doesn’t explain how you get the number returned instead of the text – and the displaythis named range has the #REF! error against it for both columns. I have liked a lot of the tips sent through but this one has me baffled as the explaination is not so thorough.
Mynda Treacy
Hi Andrew,
Thanks for your comment. The ‘displaythis’ named range is redundant and should not be in the file. I didn’t notice it was still there. As you will see from my explanation I don’t refer to it at all. Sorry for the confusion. I have updated the file now.
I’m sorry my explanation wasn’t clear enough. Please let me try to explain again how Marc achieved this:
1. he typed the numbers 10,11,12 and 20 in cells A1:A4. He then selected each cell and set up a custom number format that displayed only the reason code description (see step 1 above for how to do this).
2. Then give the cells A1:A4 a named range ‘reason_codes’.
3. Select the cell you want your data validation list in > Data tab of the ribbon > Data Validation > choose List and in the Source field enter =reason_codes
Your validation list will display the reason code description, but when you select an item from the list it will return and display the reason code number because the cell containing the data validation list isn’t formatted with the custom number format and so it displays the actual value, whereas the data validation list is referencing a range of cells that are formatted with custom number formats to only show the reason code description.
I hope that fills in the blanks for you. Please let me know if it’s still unclear.
Kind regards,
Mynda.
jed
thanks for sharing this.. i might use it in another project. right now, i was looking at the data spread over by daily basis on my monthly list with employees names on the left and the assigned work orders on the right corresponding each day of the month. Some of them are assigned in the same job per day and i need to count the number of employees working there by work orders on daily basis. please help me solve this.. thanks.
Mynda Treacy
Hi Jed,
I’m pleased you’ll find a use for it.
I’ll speak to you offline about counting the number of employees by work order etc.
Kind regards,
Mynda.