Data Validation on a calculated cell

[Guest]

I want to have data validation on a cell containing a formula. Is it
possible to have validation on the results of a formula and how do you do it?
Here is what I'm doing:

A1 (QTY) A2 (UNIT PRICE) A3 (EXTENDED PRICE)

A3 HAS THIS FORMULA =A1*A2

I want to have any value exceeding $5000 in A3 cause a data validation error
alert to appear with a message instructing what to do.

 

[JE McGimpsey]

Data validation won't work here - it only checks user entries. You can
use a worksheet event macro:

Put this in the worksheet code module (right-click the worksheet tab and
choose View Code):

Private Sub Worksheet_Calculate()
If Me.Range("A3").Value > 5000 Then _
MsgBox "A3's too high - do something!"
End Sub



Or, you could attack the problem at its source. If A1 and A2 are both
manually entered, you could use Validation on A1 and A2. Select them
both and choose Data/Validation:

Allow: Custom
Formula: =($A$1*$A$2)<5000

 

[Guest]

Data Validation only applies to manually input data. It will not work on a
calculated formula.

You could use an IF Statement in A3, like:

=IF(A1*A2>5000,"ALERT",A1*A2)

Or, in a different cell, enter:

=IF(A3>5000,"ALERT","")

This second option may give you more room to also provide instructions if
needed.

HTH,
Elkar

 

[Guest]

Elkar
Thanks ... that got me going, however I didn't do a very good job of
explaining my problem in the first place. I guess posting on a Friday after
a long week isn't a good idea.

I can get it to function by just reading A3 but in my haste to post the
problem I forgot the other conditions

Problem:
I want validation on cell A2.
1) cell A1 must have a number >0 (in other words a whole number) and
2) A2's input must not exceed 2500 and
3) A3's calculation must not exceed 2500 (A#'s calculation is =A1*A2)

Any thoughts would be appreciated.

 

[JE McGimpsey]

One way:

Select A1. Use the inputboxes and dropdowns to set validation to

Allow: Whole number
Data: greater than
Minimum: 0
Enter you error alert.

Select A2. Set validation to
Allow: Custom
Formula: =AND(A2<=2500, A1*A2<=5000)
Enter your error alert.

 

[Guest]

JE
That works but it doesn't address item-1 below. I want the A2 cell entry
halted if there is no qty input to cell A1. I want to force the person to go
back and enter a number when they have skipped it.

Any additional thoughts would be much appreciated.
Rick

 

[Guest]

How about this for Cell A2 Validation formula:

=AND(A2<=2500, A1*A2<=5000,A1>0)

HTH,
Elkar