software

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.

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(quot;A3quot;).Value gt; 5000 Then _
MsgBox quot;A3's too high - do something!quot;
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)lt;5000In article gt;,
Rick gt; wrote:

gt; I want to have data validation on a cell containing a formula. Is it
gt; possible to have validation on the results of a formula and how do you do it?
gt; Here is what I'm doing:
gt;
gt; A1 (QTY) A2 (UNIT PRICE) A3 (EXTENDED PRICE)
gt;
gt; A3 HAS THIS FORMULA =A1*A2
gt;
gt; I want to have any value exceeding $5000 in A3 cause a data validation error
gt; alert to appear with a message instructing what to do.

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*A2gt;5000,quot;ALERTquot;,A1*A2)

Or, in a different cell, enter:

=IF(A3gt;5000,quot;ALERTquot;,quot;quot;)

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

HTH,
Elkarquot;Rickquot; wrote:

gt; I want to have data validation on a cell containing a formula. Is it
gt; possible to have validation on the results of a formula and how do you do it?
gt; Here is what I'm doing:
gt;
gt; A1 (QTY) A2 (UNIT PRICE) A3 (EXTENDED PRICE)
gt;
gt; A3 HAS THIS FORMULA =A1*A2
gt;
gt; I want to have any value exceeding $5000 in A3 cause a data validation error
gt; alert to appear with a message instructing what to do.

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 gt;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.quot;Elkarquot; wrote:

gt; Data Validation only applies to manually input data. It will not work on a
gt; calculated formula.
gt;
gt; You could use an IF Statement in A3, like:
gt;
gt; =IF(A1*A2gt;5000,quot;ALERTquot;,A1*A2)
gt;
gt; Or, in a different cell, enter:
gt;
gt; =IF(A3gt;5000,quot;ALERTquot;,quot;quot;)
gt;
gt; This second option may give you more room to also provide instructions if
gt; needed.
gt;
gt; HTH,
gt; Elkar
gt;
gt;
gt; quot;Rickquot; wrote:
gt;
gt; gt; I want to have data validation on a cell containing a formula. Is it
gt; gt; possible to have validation on the results of a formula and how do you do it?
gt; gt; Here is what I'm doing:
gt; gt;
gt; gt; A1 (QTY) A2 (UNIT PRICE) A3 (EXTENDED PRICE)
gt; gt;
gt; gt; A3 HAS THIS FORMULA =A1*A2
gt; gt;
gt; gt; I want to have any value exceeding $5000 in A3 cause a data validation error
gt; gt; alert to appear with a message instructing what to do.

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(A2lt;=2500, A1*A2lt;=5000)
Enter your error alert.
In article gt;,
Rick gt; wrote:

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

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

quot;JE McGimpseyquot; wrote:

gt; One way:
gt;
gt; Select A1. Use the inputboxes and dropdowns to set validation to
gt;
gt; Allow: Whole number
gt; Data: greater than
gt; Minimum: 0
gt; Enter you error alert.
gt;
gt; Select A2. Set validation to
gt; Allow: Custom
gt; Formula: =AND(A2lt;=2500, A1*A2lt;=5000)
gt; Enter your error alert.
gt;
gt;
gt;
gt; In article gt;,
gt; Rick gt; wrote:
gt;
gt; gt; Problem:
gt; gt; I want validation on cell A2.
gt; gt; 1) cell A1 must have a number gt;0 (in other words a whole number) and
gt; gt; 2) A2's input must not exceed 2500 and
gt; gt; 3) A3's calculation must not exceed 2500 (A#'s calculation is =A1*A2)
gt;

How about this for Cell A2 Validation formula:

=AND(A2lt;=2500, A1*A2lt;=5000,A1gt;0)

HTH,
Elkarquot;Rickquot; wrote:

gt; JE
gt; That works but it doesn't address item-1 below. I want the A2 cell entry
gt; halted if there is no qty input to cell A1. I want to force the person to go
gt; back and enter a number when they have skipped it.
gt;
gt; Any additional thoughts would be much appreciated.
gt; Rick
gt;
gt; quot;JE McGimpseyquot; wrote:
gt;
gt; gt; One way:
gt; gt;
gt; gt; Select A1. Use the inputboxes and dropdowns to set validation to
gt; gt;
gt; gt; Allow: Whole number
gt; gt; Data: greater than
gt; gt; Minimum: 0
gt; gt; Enter you error alert.
gt; gt;
gt; gt; Select A2. Set validation to
gt; gt; Allow: Custom
gt; gt; Formula: =AND(A2lt;=2500, A1*A2lt;=5000)
gt; gt; Enter your error alert.
gt; gt;
gt; gt;
gt; gt;
gt; gt; In article gt;,
gt; gt; Rick gt; wrote:
gt; gt;
gt; gt; gt; Problem:
gt; gt; gt; I want validation on cell A2.
gt; gt; gt; 1) cell A1 must have a number gt;0 (in other words a whole number) and
gt; gt; gt; 2) A2's input must not exceed 2500 and
gt; gt; gt; 3) A3's calculation must not exceed 2500 (A#'s calculation is =A1*A2)
gt; gt;