data validation list selected from a range

N

Native

Hello:

Have searched for this but figure either I'm calling this the wrong
thing or is a limitation.

I have a range of cells I would like the data validation to choose
from. For example:

1 4 9 12
56 3 24 1
8 18 49 3

I'd like all of these #'s to be in my list to select from; however, all
I can get the list to accept is just one column.

Any advice?
 
D

Dave Peterson

You can put all your data in a column to retain the dropdown or you can validate
the cell using a custom formula:

With A1 the cell getting Data|Validation and the numbers in A15:D17, I used this
formula:
=COUNTIF($A$15:$D$17,A1)>0
 
G

Gord Dibben

Type them into the source dialog.

1,4,9,12 etc.

Note the comma-delimited format.


Gord Dibben MS Excel MVP
 
N

Native

Dave: tried your formula but keep getting the same message:

"The List Source must be a delimited list, or a reference to a single
row or column."

Also, I can't type in all the values b/c the list is ever changing
hence I have an array name that references a range.
 
N

Native

Dave: tried your formula but keep getting the same message:

"The List Source must be a delimited list, or a reference to a single
row or column."

Also, I can't type in all the values b/c the list is ever changing
hence I have an array name that references a range.
 
N

Native

ps - the error message was in List mode. When I put it in custom the
validation didn't work. Meaning I could type in anything and it would
allow the input.
 
N

Native

But the validation part of the data validation didn't seem to work in
custom. Using the above #s from my original post, if I typed in 124 I
would not get an input error message.
 
D

Debra Dalgleish

If there are blank cells in the named range, remove the check mark from
'Ignore blank' in the data validation dialog box.
 
N

Native

bingo! (take the check mark out)

also, i played around with it and if I took the range name out and just
used the cell references it worked with the check still on ????

is there anyway to get this to work with a list? (ie-a pull down)
 
D

Dave Peterson

Post the formula you used. And the addresses that contain the table and the
address of the cell that got the format|conditional formatting formula.
 
D

Debra Dalgleish

As Dave said, you'd need the data in a single column if you want to
create a dropdown list.
 
Top