Is it possible to have the data (choices) shown when clicking the drop down list to appear as numbers (or any text other than the actual text from validation data source)?

Let's say the choices (drop-down-list) are: (examples: titles of seminar)

  • Approaches to Sustainable Food and Agriculture
  • Economic Sanctions in U.S. Foreign Policy
  • Trials of the Century
  • The Craft of Narrative Journalism
  • And it goes on to hundreds of these...

As you can see it is not convenient to have those lengthy sentences appear in the cells' drop down list. Therefore I need something like shortcuts such as numbers (or short text) so that when I click the drop down list icon (on the right side of the cell) it will show e.g.

  1. .
  2. .
  3. .
  4. ......

And when I click the number i.e. "1" the cell will be filled with "Approaches to Sustainable Food and Agriculture" - (depending on what data associated with the shortcuts, in above example "1" represents "Approaches to Sustainable Food and Agriculture")

Also, when I'm using the drop-down-list I always hold the hard-copy/paper of the seminar titles' list with the numbers on them hence I know which number represents which titles i.e.

  1. Approaches to Sustainable Food and Agriculture
  2. Economic Sanctions in U.S. Foreign Policy
  3. Trials of the Century
  4. The Craft of Narrative Journalism

If possible please send me an example in excel format to my email steven.antonius@yahoo.com

Thanks in advance

NB: I don't mind using other methods than drop-down-list; however, IF or Vlookup functions won't help because it will require at least two cells i.e. one containing the formula (which will show the result associated with the shortcut) and another to type the shortcut e.g. "3". I need the shortcut and result to be on the same cell, that's why I asked how to achieve what I need using drop-down-list.

  • What do you have already? – Raystafarian Apr 20 '15 at 17:26
  • 1
    The paradigm of a drop down list is that the person sees the choices and picks one. If he sees a nonsense list and has to pick one blindly to see what it is, it becomes pretty much useless. Sort of like Nancy Pelosi's "You have to approve it to learn what's in it." – fixer1234 Apr 20 '15 at 17:53
  • @fixer1234: I realize that the question doesn't say so, but my interpretation when I read it was that this would be something that the users would already be familiar with (and that might be documented statically on the sheet); e.g., something like 1 ↔ "strongly agree", 2 ↔ "agree", 3 ↔ "mildly agree", 4 ↔ "neutral", 5 ↔ "mildly disagree", etc... – G-Man Says 'Reinstate Monica' Apr 20 '15 at 20:34
  • @G-Man: I can see your point for that type of example, but the question describes a more complicated scenario and displaying the text after selection, which wouldn't be needed for the kind of example you suggested. My reading was that the choices require a lot of text, making it hard to find the desired one. Hiding it was a way to limit how much is shown, for easier reading. However, the differences between the choices is subtle, and benefits from seeing them side-by-side. The problem needs a solution, but IMHO, the approach in the question would make it worse. – fixer1234 Apr 20 '15 at 20:46
  • @G-Man I just made simpler examples so that you get what I meant. Thank you for trying to help. – Steven Antonius Apr 21 '15 at 7:09

My suggestion is a combination of list and vlookup() in a separate cell. As with most things in Excel, there is probably more than one way of doing it, but this is the first thing that comes to mind.

I.E. limit answer to text in a defined list using data validation. To the right of each item, specify the number you want to translate to. Then, in a separate cell next to each question, use something like =IFERROR(VLOOKUP(E3,$B$6:$C$9,2,0),"") as your formula.

I don't know if this answers your question. I'm not sure I completely understand how you want this to appear. I hope this helps.

  • This will require at least two cells i.e. one containing the formula (which will show the result associated with the shortcut) and another to type the shortcut e.g. "3". I need the shortcut and result to be on the same cell. Thanks for trying to help :) – Steven Antonius Apr 21 '15 at 7:37
  • Well, how are your macro chops? I don't know of a way to do this without a macro. You are basically asking that Excel show one set of values (which can only be done with data validation) and set a different set of values in the cell (which goes against said validation). In my opinion, this can only be done with a macro. (no, I'm not providing the code.) – rfportilla Apr 21 '15 at 16:05

Steven, here is one possibility if you don't want to go the macro route.

Have your master list of titles as a named range with the unique number next to it (e.g., A. Approaches to Sustainable Agriculture, B. Economic Sanctions in U.S. Foreign Policy, A1. Trials of the Century). Make sure you have enabled autocomplete in cell (in backstage view, Options, Advanced, check Enable AutoComplete for cell values).

After you have entered a master list in the data entry column then you can hide those rows. By themselves, preceding numbers won't work with autocomplete. The initial character needs to be text (hence the A., B., A1. values in my list). As long as your data entry is contiguous to the master list of valid entries you can just type the characters before the period to display what you want, then hit enter (or arrow down) to accept the resulting display and move the cursor.

If you do not want the shortcut values to precede the entries after you've entered your data you can use a regular expression with a wildcard to do a search and replace in your data entry range: enter "*. " without the quotation marks---that is, enter an asterisk, a period, and a space---in the search box of the search and replace dialog---NOTE: this assumes your list items do not already include periods).

The reason I'm ignoring data validation is because you would still need to activate the dropdown list and make a selection---you cannot just enter the shortcut number. It seems to me that is still pretty laborious for lots of entries. I don't know that data validation really helps you here although you could still do it and set the list to your hidden master list named range. I hope this helps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.