Create Drop-down List From Named Range

Question

I have created a drop-down list using the range shown to the right in the below image.  I haven't played much with making lists, so this was just a proof of concept.

Now I would like to refine the way this list is handled.  Instead of having the list take up space on the sheet, I would like to convert it into a named value that contains the list, and I can't use macros since the file will be used by people with macros disabled.  As an example, I am thinking about something like the following (Which does not work).

Is it possible to do it this way?  If not, then the cleanest option that I can think of is to put the list on a separate sheet and hide the sheet.

Thanks for any help that you can offer.

--Tom

Answer 1

A named range refers to cells on a worksheet, not to actual text that you type in. However, you can type the text that you want for the drop-down list instead of it referring to cells or to a named range.

In the source for the list, where you probably typed the named range of "Result_List" type "Fixed,NotFixed,Testing" without the double quotes.

Answer 2

Hi Tom,

Did you refer to Jason's suggestion to create a drop down list from a name range?

Regards,

Joanne

Answer 3

Jason,

Thanks for the reply.  Sorry that it has taken so long to get back to you.  Things have been hectic.

I actually tried your suggestion before posting my question.  One of my key goals, and maybe this was not clear enough in my original post, is that the first value on the list is blank.  Notice how the first cell in the range to the right is blank, and then on the actual drop-down list, the first option is nothing.  I have not been able to replicate that by typing the values into the Source box for the drop-down list.  I tried it this way, " ,Fixed,Not Fixed,Testing" (Without the quotes, first value is a space) and Excel will accept that, but it ignores the space.

--Tom

Answer 4

Hi Tom,

Thanks for the update.

There isn't an out of the box way in Excel to achieve your requirement without ignoring the space.

Thanks for sharing your experience with us. And welcome to submit your feedback in our Excel UserVoice if you'd like to achieve this using an Out of the box feature. UserVoice is actively monitored by related team. Providing your feedback in our UserVoice will help better our products and services.

We appreciate your understanding.

Regards,

Joanne