As I didn't get any answer to my previous question on similar subject, I'll
make a new attempt with somewhat simpler examples.

Let's me have a custom data validation defined for some range of cells. P.e.
I select the range A1:A10 and set custom validation formula as
=AND(A1=0,A1<=100).
Now only entries between 0 and 100 (or empty cell) are allowed. When tested,
data validation works.

Now I define (the range A1:A10 is selected) a named range MyRange
=$A$1:$A$10
, and modify the data validation formula (for cell A1) to
=AND(MyRange=0,MyRange<=100)
Now any entry is allowed.

Maybe it's because I'm looking for particular entry in named range? Let's
try another condition for same range.
=(SUM($A$1:$A$10)<=100)
I can enter numeric entries into range only until their sum doesn't exceed
100 now. It's OK.

But when the validation formula will be
=(SUM(MyRange)<=100)
then I can enter any values, and the sum isn't checked anymore.

When I searched with google for subject, I didn't find any restrictions for
using named ranges in data validation. But it looks like for Custom data
validation such limit exists, and I find this very annoying.

Has somebody any enlighting ideas about subject? Thanks in advance for any
tips.

--
Arvi Laanemets
(When sending e-mail, use address arvil<Attarkon.ee)

Hi
for the first it's clear why it does not work: Originally you're
comparing one single cell reference (e.g. A1) But now you're using a
cell range (A1:A10)

--
Regards
Frank Kabel
Frankfurt, Germany

"Arvi Laanemets" schrieb im Newsbeitrag
...
As I didn't get any answer to my previous question on similar
subject, I'll
make a new attempt with somewhat simpler examples.

Let's me have a custom data validation defined for some range of
cells. P.e.
I select the range A1:A10 and set custom validation formula as
=AND(A1=0,A1<=100).
Now only entries between 0 and 100 (or empty cell) are allowed. When
tested,
data validation works.

Now I define (the range A1:A10 is selected) a named range MyRange
=$A$1:$A$10
, and modify the data validation formula (for cell A1) to
=AND(MyRange=0,MyRange<=100)
Now any entry is allowed.

Maybe it's because I'm looking for particular entry in named range?
Let's
try another condition for same range.
=(SUM($A$1:$A$10)<=100)
I can enter numeric entries into range only until their sum doesn't
exceed
100 now. It's OK.

But when the validation formula will be
=(SUM(MyRange)<=100)
then I can enter any values, and the sum isn't checked anymore.

When I searched with google for subject, I didn't find any
restrictions for
using named ranges in data validation. But it looks like for Custom
data
validation such limit exists, and I find this very annoying.

Has somebody any enlighting ideas about subject? Thanks in advance
for any
tips.

--
Arvi Laanemets
(When sending e-mail, use address arvil<Attarkon.ee)

Hi

Enter the same formula as used in data validation into adjacent column, and
it works for all 4 examples exactly as needed.

But you are right - when I defined a named range as
=$A1
with cell A1 activated, then the custom validation formula
=AND(MyRange=0,MyRange<=100)
did work. I.e. named values are allowed, but any named ranges are banned, or
what? Not very encouraging for my purpouses, as I want to restrict any time
interval overlapings for item on given date in table (Date, Item, From, To)
with variable number of rows. So using dynamic named ranges would be
preferable.


--
Arvi Laanemets
(When sending e-mail, use address arvil<Attarkon.ee)



"Frank Kabel" wrote in message
...
Hi
for the first it's clear why it does not work: Originally you're
comparing one single cell reference (e.g. A1) But now you're using a
cell range (A1:A10)

--
Regards
Frank Kabel
Frankfurt, Germany

"Arvi Laanemets" schrieb im Newsbeitrag
...
As I didn't get any answer to my previous question on similar
subject, I'll
make a new attempt with somewhat simpler examples.

Let's me have a custom data validation defined for some range of
cells. P.e.
I select the range A1:A10 and set custom validation formula as
=AND(A1=0,A1<=100).
Now only entries between 0 and 100 (or empty cell) are allowed. When
tested,
data validation works.

Now I define (the range A1:A10 is selected) a named range MyRange
=$A$1:$A$10
, and modify the data validation formula (for cell A1) to
=AND(MyRange=0,MyRange<=100)
Now any entry is allowed.

Maybe it's because I'm looking for particular entry in named range?
Let's
try another condition for same range.
=(SUM($A$1:$A$10)<=100)
I can enter numeric entries into range only until their sum doesn't
exceed
100 now. It's OK.

But when the validation formula will be
=(SUM(MyRange)<=100)
then I can enter any values, and the sum isn't checked anymore.

When I searched with google for subject, I didn't find any
restrictions for
using named ranges in data validation. But it looks like for Custom
data
validation such limit exists, and I find this very annoying.

Has somebody any enlighting ideas about subject? Thanks in advance
for any
tips.

--
Arvi Laanemets
(When sending e-mail, use address arvil<Attarkon.ee)

Change the custom data validation formula to:

=SUM(INDIRECT("MyRange"))<=100

and it should work as expected.

Arvi Laanemets wrote:
Hi

Enter the same formula as used in data validation into adjacent column, and
it works for all 4 examples exactly as needed.

But you are right - when I defined a named range as
=$A1
with cell A1 activated, then the custom validation formula
=AND(MyRange=0,MyRange<=100)
did work. I.e. named values are allowed, but any named ranges are banned, or
what? Not very encouraging for my purpouses, as I want to restrict any time
interval overlapings for item on given date in table (Date, Item, From, To)
with variable number of rows. So using dynamic named ranges would be
preferable.




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

Hi

No, It doesn't. And the formula
=SUM(INDIRECT("MyRange"))
in worsheet cell returns an #REF error



--
Arvi Laanemets
(When sending e-mail, use address arvil<Attarkon.ee)


"Debra Dalgleish" wrote in message
...
Change the custom data validation formula to:

=SUM(INDIRECT("MyRange"))<=100

and it should work as expected.

Arvi Laanemets wrote:
Hi

Enter the same formula as used in data validation into adjacent column,
and
it works for all 4 examples exactly as needed.

But you are right - when I defined a named range as
=$A1
with cell A1 activated, then the custom validation formula
=AND(MyRange=0,MyRange<=100)
did work. I.e. named values are allowed, but any named ranges are
banned, or
what? Not very encouraging for my purpouses, as I want to restrict any
time
interval overlapings for item on given date in table (Date, Item, From,
To)
with variable number of rows. So using dynamic named ranges would be
preferable.




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

What do you get on the worksheet if you enter the formula:
=SUM(MyRange)

Arvi Laanemets wrote:
Hi

No, It doesn't. And the formula
=SUM(INDIRECT("MyRange"))
in worsheet cell returns an #REF error





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