Data Validation on Locked Cell

[robbyp]

First, I would like to know if I can provide data validation on a cell that
has a formula in it (i.e. a cell that will not have a user enter a value). If
not, then I'm sol. If not, here's my scenario:

I have a cell that I do not want to calculate to over 450. There is a
formula in this cell that calculate 5 other summary cells. What I want to do
is setup validation so that if the calculation of this cell goes over 450 a
Stop error comes up. I know I can do this by setting validation rules for all
of the cells I will enter data into, but that's over 100 cells that already
have their own validation. I just want to know if there is a way to set this
validation rule on a cell that will have a formula in it and is locked so
users cannot enter data.

Thanks.

 

[Dave Peterson]

Data|validation is to stop the user from typing something invalid.

How about using an adjacent cell with a formula like:

=if(a1>450,"Error!","")

 

[Chip Pearson]

Validation applies only to values typed in by the user. I has no
effect on calculated results. You could use Conditional Formatting to
change the cell to red if some value is exceeded. Another way to
prevent the value of a cell from going above some value is to use the
MIN function:

=MIN(your_formula,MaxAllowableValue)

Here, the result will be the lesser of your formula and the
MaxAllowableValue value, 450 in your case.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)