Excel2000: Data validation using values returned by named ranges

[Arvi Laanemets]

Hi

I have set the data validation for cell B5 (Custom)
=AND(B5>=MAX(7,JKRows,EKRows))
where JKRows and EKRows are named ranges (returning at moment values 3 and
0).

Somehow data validation accepts any values. The same formula in some cell
returns to False whenever the value entered into cell B5 is less than 7. And
when I checked out, I discovered that p.e. with data validation formula
=(B5>=JKRows)
(JKRows=3) also data validation doesn't work. It looks like it can't use
named ranges in this case - but it can with validation list?

?
Thanks in advance for any help

 

[Debra Dalgleish]

Is one of the named ranges blank? In the data validation dialog box,
remove the check mark from 'Ignore blanks', and it may solve the problem.

BTW, the AND is unnecessary in your formula. You could use:
=B5>=MAX(7,JKRows,EKRows)

 

[Arvi Laanemets]

Hi

Is one of the named ranges blank? In the data validation dialog box,

No, they count used rows on 2 sheets, so they have always values >=0
EKRows=COUNTIF('EK1'!$A:$A;">0")
JKRows=COUNTIF('JK1'!$A:$A;">0")
And as I said, I checked with a single condition (JKRows), which had value
3, too.

remove the check mark from 'Ignore blanks', and it may solve the problem.

Tried, although there was little hope it'll help. It didn't

BTW, the AND is unnecessary in your formula. You could use:
=B5>=MAX(7,JKRows,EKRows)

Thanks. I had the formula with AND() at start, until I realized that I can
use MAX(), but I forget to remove it from formula.

 

[Debra Dalgleish]

Are EKRows and JKRows named ranges on the worksheet, or named formulas,
created in the Define Name dialog box?

If they're defined formulas, they don't seem to work in this case.
You could use a formula on the worksheet to refer to these names, e.g.
=JKRows
then refer to those cells in the data validation formula.

 

[Arvi Laanemets]

Hi

They are created from Insert.Name.Define, but they return a value, not a
range. It looks like I have to put them onto worksheet anyway.