Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I want to make a survey with Excel and I therefore have defined the cells for the answers as a dropdown cell which only accepts answers from a certain list, e. g.:

enter image description here The two Lists List1 and List2 (yellow cells) are the possible answers for the questions in Block 1.x resp. 2.x (blue) . There might be a block 4 with more questions, which again use List1 for their possible answers.

My problem is: I'd like to be able to use the autocompleate feature to fill in the blue cells with the dropdown menu, so that the user only types 5 and it automatically expands to "5: extremely important" or "5: extremely difficult".

According to my research on the www, this should be possible if I add the list with possible answers directly above the cells where autocomplete should work (I did this with the green helper cells which could be hidden) . But I have to enter at least 4 characters 5: e to get the autocompleted suggestion.

Is there a way to make autocomplete already replace a "5" by the corresponding valid term?

As the survey file shall be distributed to a lot of people "outside", I can not use VBA magic because it may be blocked on their computer and might not work.

EDIT: it seems to have to do with the numbers I use: If I'd start my List items with A, B, C instead of 1, 2, 3, it would work perfectly. Excel seems to ignore the pure numbers when they are entered and does not try to autocomplete them.. is there a workaround?

(I hope it is clear what I want, it seems a little difficult to explain.)

share|improve this question
up vote 1 down vote accepted

You are correct that it's the numbers. Since Excel's primary function is a spreadsheet for summing, pivoting, etc, it does treat numbers differently. I know of no way to disable this function, so the quick way around this would be to start with a letter. Even if you just put a0, a1, etc it should allow the autocomplete to work. However, I may have a better alternative for you.

Using the Data Validation tool (from the Data tab on the ribbon) you can set up a dropdown list in each cell, as well as requiring only answers from the dropdown. To enable this, write your answers somewhere out of sight, on another sheet, or in a column you can hide. Next, select any cells that should use the dropdown list of answers and click the Data Validation button. You will be presented with a screen like this:

enter image description here

Change Allow to List and then click the button in the Source text box. This will let you to select the cells where the answers will come from. You may want to change the Input and Error tabs as well to prompt the user how to answer or to correct them if they try to type invalid data. Once you click OK, the dropdowns will be enabled on the cells:

enter image description here

share|improve this answer
thanks for your answer! The data validation dropdown was already implemented - sorry, I thought it would be visible in the screenshot and I did not know the correct english term for it, as my MS Office is a non-english version. – MostlyHarmless Oct 3 '12 at 9:15
Ah. I do see now that you mentioned the dropdown in your opening sentence, and I see that in the revisions list you used to have a picture showing the dropdown. The only other suggestion I could make would be to just switch to letters, such as A: extremely important, B: very important, etc. If you needed to sum the results in a formula, having that as your first character would not be hard to account for. – techturtle Oct 3 '12 at 16:44
yes, an A-E scale would solve the problem and it would be no problem for me to convert it into numbers later, if "calculations" with the results like MEAN would be necessary. However I find letters much more un-intuitive than numbers for those who have to fill in the survey questionnaire... :-( – MostlyHarmless Oct 3 '12 at 18:34

On the basis of "why fix with a scalpel when a sledgehammer would serve" I would attack this issue with a lookup table (say J2:K7 in the example) and use a formula immediately to the right of the data entry boxes. J2:J7 also serving as a data validation list. Then tinker with the appearance to suit.

SU482431 example

share|improve this answer
that's also a good idea, but too late. I finally decided to leave the numbers out and use the answers like "very difficult" etc. as a list for the dropdown, which allowed to select them by their first character (and autocompletion, which however required to have the possible values directly in hidden cells above the fields to fill out - a little ugly, but ok), which is nearly unique. – MostlyHarmless Oct 12 '12 at 21:18

You must log in to answer this question.

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