Excel2000: Custom data validation and named ranges
 

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

Hi


"Debra Dalgleish" wrote in message
...
What do you get on the worksheet if you enter the formula:
=SUM(MyRange)

It returns the sum, of course

Btw. =SUM(INDIRECT("MyRange")) returns an error when the named range is
dynamic, like
MyRange=OFFSET(Sheet1!$A$1,,,COUNTIF(Sheet1!$A:$A, "<"),1)
With non-dynamic named range INDIRECT works.


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

In your first message you said:

Now I define (the range A1:A10 is selected) a named range MyRange
=$A$1:$A$10

My suggestion was for that, not for a dynamic range.

Arvi Laanemets wrote:
Hi


"Debra Dalgleish" wrote in message
...

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


It returns the sum, of course

Btw. =SUM(INDIRECT("MyRange")) returns an error when the named range is
dynamic, like
MyRange=OFFSET(Sheet1!$A$1,,,COUNTIF(Sheet1!$A:$A, "<"),1)
With non-dynamic named range INDIRECT works.




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

You could, instead, name just the first cell in the range, e.g.
MyRange: =Sheet1!$A$1

In the custom data validation, use the formula:

=SUM(OFFSET(MyRange,0,0,COUNTA(A:A),1))<=100

Debra Dalgleish wrote:
In your first message you said:

Now I define (the range A1:A10 is selected) a named range MyRange
=$A$1:$A$10

My suggestion was for that, not for a dynamic range.

Arvi Laanemets wrote:

Hi


"Debra Dalgleish" wrote in message
...

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



It returns the sum, of course

Btw. =SUM(INDIRECT("MyRange")) returns an error when the named range is
dynamic, like
MyRange=OFFSET(Sheet1!$A$1,,,COUNTIF(Sheet1!$A:$A, "<"),1)
With non-dynamic named range INDIRECT works.






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

Hi

At same start I mentioned, that those examples are simplified. The original
validation formula (for cell D2) was
=(IF(AND($B2="",C2=""),0,SUMPRODUCT(--($B$2:$B$65536=$B2);--($C$2:$C$65536=$
C2);--($D$2
:$D$65536<$D2);--($E$2:$E$65536=$D2)))=0)
And probably I have to add a couple of additional checks. The idea was
replace range references in formulas with dynamic named ranges to avoid
unused rows to be taken into account. Implementing OFFSET's directly into
formula will not do - the formula will be simply huge.


Arvi Laanemets


"Debra Dalgleish" wrote in message
...
You could, instead, name just the first cell in the range, e.g.
MyRange: =Sheet1!$A$1

In the custom data validation, use the formula:

=SUM(OFFSET(MyRange,0,0,COUNTA(A:A),1))<=100

Debra Dalgleish wrote:
In your first message you said:

Now I define (the range A1:A10 is selected) a named range MyRange
=$A$1:$A$10

My suggestion was for that, not for a dynamic range.

Arvi Laanemets wrote:

Hi


"Debra Dalgleish" wrote in message
...

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



It returns the sum, of course

Btw. =SUM(INDIRECT("MyRange")) returns an error when the named range is
dynamic, like
MyRange=OFFSET(Sheet1!$A$1,,,COUNTIF(Sheet1!$A:$A, "<"),1)
With non-dynamic named range INDIRECT works.






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