Excel2000: Data validation using values returned by named ranges

  • Thread starter Thread starter Arvi Laanemets
  • Start date Start date
A

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
 
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)
 
Hi


Debra Dalgleish said:
Is one of the named ranges blank? In the data validation dialog box,
Click to expand...

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.
Click to expand...

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)
Click to expand...

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.
 
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.
 
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.
 
Back
Top