data validation

  • Thread starter Thread starter alex
  • Start date Start date
A

alex

I've coded a big spreadsheet application which has more
than 100 worksheets and 10000 lines VBA code. I have a
question which need some expert to give some insight:

I set a validation on cell "L12" in worksheet "S2". The
validation criteria is:
Allow -- "Custom"
Formula -- "=B32"

In cell "B32", a formula is defined like this:

=AND(ID!T11<=ID!D77-65,L12<=3787,OR(ID!N16="X",ID!
Q16="X",Adjust!D30=1))

The key part is "L12<=3787" in the formula.

So when I enter a value in cell "L12" which is greater
than 3787 when other conditions are ok, I'm expecting
Excel to stop it.

In my computer, it works. But some of my clients complain
that it does not work in their computer. I and the clients
are all using Excel 2000.

The impression I have is that the problem might caused by
the sequence of calculation and validation:

In my computer, after I enter the wrong value, Excel
calculates the formula first and then validates the
entered value, so the entered value is stopped.

In the clients computer, Excel validates the entered value
first and then does the calculation. So the wrong value
passed. The reason why I think so is that when the clients
try to enter a value less than 3787, should be a valid
value, after entered the wrong value, Excel stops the
value.

My question is:

Does this problem caused by some configuration parameter(s)
or
Is it a bug in early Excel 2000 version?

Thanks,

Alex
 
hi, alex !
I set a validation on cell "L12" in worksheet "S2". The validation criteria is:
Allow -- "Custom" | Formula -- "=B32"
In cell "B32", a formula is defined like this:
=AND(ID!T11<=ID!D77-65,L12<=3787,OR(ID!N16="X",ID!Q16="X",Adjust!D30=1))
The key part is "L12<=3787" in the formula.
... a value in cell "L12" ... greater than 3787 ... other conditions are ok, I'm expecting excel to stop it.
... some of my clients complain that it does not work in their computer. I and the clients are all using excel 2000.
... my computer ... calculates the formula first and then validates the entered value, so the entered value is stopped.
... clients computer ... validates the entered value first and then does the calculation. So the wrong value passed [...]
My question is:
Does this problem caused by some configuration parameter(s)
or
Is it a bug in early Excel 2000 version?
Click to expand...

[it's just a though]
1.- your client's computer is set to 'manual' calculation
2.- ANY value in 'L12' is entered WHILE cell 'B32' is still 'evaluated' as TRUE
[prior to any recalc and -possibly- changes to other 'ex-true' conditions]

HTH,
Héctor.
[MS MVP]
 
Back
Top