Why does this not work?
Posted by Cliff Myers on August 04, 2000 8:59 PM
In a purchase journal I want to issue a warning when an account has exceded its budget. Cells j42:o42 shows the remaining amount in each account. Cells f9:f38 are where we enter the account number. The formula I used is =if(and(j42:O42=0, f9:f38=610, f9:f38=612, f9:f38=613, f9:f38=617, f9:f38=624, f9:f38=625, f9:f38=614), f9:f38). This formula works when the account reaches $0 but the problem is, it also displays the warning when the account number is entered and the account is still positive. Where have I errored in the above formula?
Posted by Celia on August 06, 0100 2:49 AM
Cliff
Any formula that is used in Excel's Data Validation tool must evaluate to TRUE or FALSE.
Also, data validation only works on cells if the data is manually input - not if the data in the cell is a result of a formula, or is entered by a macro, or by copying from another cell, or by filling.
Presumably the adverse budget variances are not input manually but are from a formula?
I don't understand your formula, but if you merely want to be alerted when any manual input to a cell in a particular range(Say A1:A12) has a value of 0 or less, then select cell A1 and put this formula in Data Validation :-
=A1>0
Select A1, Copy and PasteSpecial/Format to A2:A12.
There are other options. Here are some.
For the purpose of illustration, assume that your budget variance amounts are in A1:A12 and the corresponding account numbers are in B2:B12.
WORKSHEET FORMULA
Put this formula in C1 and drag it down to A12 :-
=IF(($A$1:$A1)<0,"A/c " &($B$1:$B1)& " is Overspent","").
CONDITIONAL FORMATTING
1.HIGHLIGHT NEGATIVE & ZERO AMOUNTS IN COLUMN A
Select A1.
Go to Format/Conditional Formatting...
Select Cell Value Is / Less Than or Equal To.
Input 0 in the third box.
Select Format... and set the format you want.
Select A1, Copy, and PasteSpecial/Format to A2:A12.
2.HIGHLIGHT COLUMN B RE NEGATIVE & ZERO AMOUNTS IN COLUMN A.
Select B1.
Go to Format/Conditional Formatting...
Select Formula Is.
Type in this formula : =OR(A1<0,A1=0)
Select Format... and set the format you want.
Select B1, Copy, and PasteSpecial/Format to B2:B12.
Also, instead of Conditional Formatting, a custom number format could be done for Column A (e.g. Zeros & negatives in green(or some other distinctive colour) and/or prefixing/suffixing zeros/negatives with text such as "Overspent").
Celia
Posted by Celia on August 06, 0100 2:52 AM
Correction
The last line of the second paragraph should read :-
Select A1, Copy and PasteSpecial/Validation to A2:A12.
Posted by Celia on August 04, 0100 10:52 PM
Cliff
I can't follow any of this. It would help if you could post some sample data and the formula's actual syntax (copied and pasted from your workbook).
Celia
Posted by cliff on August 05, 0100 11:39 PM
Thanks for the reply, I have my journal notifing of overspent accounts by displaying a notice in a cell above the budgets. But what I'm wondering is, can I use data validation to pop up a warning when an account is overspent? I've tried to use the same IF formula I used in the cell above the budgets in data validation but I get an error message. What formula should I use in data validation to notify a user when an account has reached 0 or below?
