how to define list of items as named range

[Tkeller]

What I want to do is probably relatively simple.

I want to use a drop down list that contains several listed items (e.g. pants, shirts, socks, shoes), but I do not want to have to list these on my spreadsheet. What I would like to do is define these items in a named "formula" (e.g. with the name "clothes", where clothes will have all of these elements). Then, I would like to use this named range in my drop down box, so that the user can select which clothes item he/she wants. The goal here is to not have to physically list the items on the spreadsheet, instead having them contained in a named range formula.

Thanks!

 

[Andrew Poulsom]

You can type the list directly into the Source box on the Data Validation dialog.

 

[T. Valko]

What I want to do is probably relatively simple.

I want to use a drop down list that contains several listed items (e.g. pants, shirts, socks, shoes), but I do not want to have to list these on my spreadsheet. What I would like to do is define these items in a named "formula" (e.g. with the name "clothes", where clothes will have all of these elements). Then, I would like to use this named range in my drop down box, so that the user can select which clothes item he/she wants. The goal here is to not have to physically list the items on the spreadsheet, instead having them contained in a named range formula.

Thanks!

As long as there aren't too many items you can enter the items separated by a comma within the validation user form. For example:

Item1,Item2,Item3,Item4

The user form refedit (the little box where you'd enter the items) will only accept up to 255 characters.

 

[Tkeller]

Thanks, but I don't want to use data validation. I want to use a drop down box (from the forms menu) and then have these items selectable in the drop down menu. I don't want to have to type the items on my spreadsheet in order to refer to them - I want to instead refer to a named range and then be able to have that named range include this list of items.

Thanks.

 

[T. Valko]

Thanks, but I don't want to use data validation. I want to use a drop down box (from the forms menu) and then have these items selectable in the drop down menu. I don't want to have to type the items on my spreadsheet in order to refer to them - I want to instead refer to a named range and then be able to have that named range include this list of items.

Thanks.

So, you want to use a forms combo box?

I don't think you can do it the way you want. You need to use a range of cells to hold the list. You might be able to do it with VBA code but that seems like over-complicating something that's normally relatively simple.

 

[Tkeller]

Thanks. Just to be clear,there is no way to define a named range as an array of items? I would always have to reference cells on a worksheet?

 

[Andrew Poulsom]

Yes you can define a name Clothes like this:

={"pants","shirts","socks","shoes"}

and the formula:

=INDEX(Clothes,2)

will return shirts. But you can't use the name as the source of a Forms ComboBox.