data validation list selected from a range
 

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?

data validation list selected from a range
 

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

Native wrote:

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?

--

Dave Peterson

data validation list selected from a range
 

Type them into the source dialog.

1,4,9,12 etc.

Note the comma-delimited format.


Gord Dibben MS Excel MVP

On 19 Jan 2006 08:52:16 -0800, "Native" wrote:

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?

data validation list selected from a range
 

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.

data validation list selected from a range
 

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.

data validation list selected from a range
 

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.

data validation list selected from a range
 

It doesn't use a list. It uses a custom formula.



Native wrote:

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.

--

Dave Peterson

data validation list selected from a range
 

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.

data validation list selected from a range
 

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

Native wrote:
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.



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

data validation list selected from a range
 

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)

data validation list selected from a range
 

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.

Native wrote:

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.

--

Dave Peterson

data validation list selected from a range
 

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

Native wrote:
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)



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html