Excel2000: Custom data validation and named ranges

[Arvi Laanemets]

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.

 

[Frank Kabel]

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)

 

[Arvi Laanemets]

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]

Change the custom data validation formula to:

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

and it should work as expected.

 

[Arvi Laanemets]

Hi

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

 

[Debra Dalgleish]

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

 

[Arvi Laanemets]

Hi

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]

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.

 

[Debra Dalgleish]

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

 

[Arvi Laanemets]

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