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.
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
- 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.
Download the Excel workbook. (Note: this is a .xlsx file, not a .zip file. Please ensure your browser doesn't incorrectly change the file extension.)
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."
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 🙂