Drop down list from cell values from a non delimited list

[ceytl]

I want to do a drop list, but excel says the list has to be delimited.

Is there a way to do a drop down list from values in the same column, but with different rows that are not next to each other?

Here is a sample, the yellow cells are the values I want in my drop down menu.

Book4
	A	B	C	D
1
2	AAF2	10
3	AAF3	30
4	AAF4	70
5	AAF5	50
6	AAF6	60
7	AAF7	20
8	AAF8	800
9	AAF9	90
10	AAF10	100
11	AAF11	110
12	AAF12	120
13	AAF13	500
14	AAF14	140
15	AAF15	150
16	AAF16	1000
17	AAF17	20
18	AAF18	180
19	AAF19	250
20	AAF20	200
21	AAF21	400
22
Sheet1

 

[Lee J]

Hi Ceytl,

You could use data validation and then just type in the values you want like below:

 

[ceytl]

Thanks for your help!

The only problem I see is that it's limited to 256 characters, including the separators.

If I want to do more, is there another solution?

Thanks!

 

[Lee J]

There is another way, not 100% sure you are going to like it?

Create a list that contains only the choices you want to use and then reference the range still using data validation tool, you can do this two ways:

Directly by clicking in the source box and then selecting your range or making a named range (selecting your data and then in the box to the left of the formula bar you should see a cell reference like A1 give the list a name by overtyping it). Then in the source box put =(your list name) no brackets