validated data range in a cell containing a formula

Posted by clint aird on June 19, 2001 1:17 AM

to all who wish to help

i have a cell in a spreadsheet that verifies data has been entered correctly by subtracting a value from another. i would like to have an error message appear if the value is not within the range of + or - $5. if i try and use the data-validation settings, it cannot produce an error message as this function does not work with a cell containing a formula - only if the data is entered by hand.
having it audit the data is a bit useless, as I need this as a safegaurd to prevent ignorant people (ie the boss) from putting in data (they may or may not be too lazy to perioically audit the data to check for the error)
any macros or suggestions that will check this for me?
suggestions much appreciated!
thanks
clint

Posted by Aladin Akyurek on June 19, 2001 1:55 AM

What is the formula that you use in your data validation cell?

And, what is the norm with which you want to compare the number entered as input?

Aladin

==========

Posted by clint aird on June 19, 2001 5:02 PM

Hi there
the formula in the validation cell is a simple =CellA-CellB
it is a quoting spreadsheet that has two columns - one column shows the cost to the company, and the other shows the charge out. to make sure that the source data in each is the same, the totals of each are subtracted. ideally they are supposed to be the same, but due to decimal place rounding they are normally plus or minus $5.
Nothing is physically input into this validation cell, it simply calculates from the two totals.
did that make some (any) sense?
thanks
clint

Posted by Aladin Akyurek on June 19, 2001 10:56 PM

Hi Clint,

Data Validation is used to control the input,
an irrelevant option in your case. Here a few suggestions:

(1) Signal that the difference is too big to ignore:

=IF(ABS(CellA-CellB)>5,"Error: Difference Too Big",CellA-CellB)

(2) Highlight the cell if absolute difference is bigger than 5 (BTW, is a diff of $5 really acceptable?)

Activate the cell where you compute the difference between CellA and CellB, activate the option Format|Conditional Formatting, select "Formula is" on Settings Dialog, and type the following formula:

=ABS(A1)>5 [ substitute the real cell ref for A1 that you use ]

Select Format and give the cell e.g., a red background.

Hope this helps.

Aladin Hi there

Posted by clint aird on June 20, 2001 4:50 PM

Hi Aladin

thanks for putting that together. it works nicely, but part of the problem I encounter is that it does not flag the error. the spreadsheet is large and you can't always see the problem you have created until much later.
is there a way to have it bring up a visible message or something?

thanks

Clint Hi Clint, Data Validation is used to control the input,

Posted by Aladin Akyurek on June 20, 2001 5:25 PM

Which one...

did you take up? The second at least gives color feedback. If you are after some auditive feedback or something that pop us to signal the error, the best thing you can do is to ask for VBA code for it.

Aladin

================ Hi Aladin thanks for putting that together. it works nicely, but part of the problem I encounter is that it does not flag the error. the spreadsheet is large and you can't always see the problem you have created until much later.