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?

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

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?

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

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.

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

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.

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

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)

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

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