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.:
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.)
