Here is my problem. I need to compare calculated data, and my
spreadsheet program will be used by unskilled people.

There are multiple sets of 3 columns each for input of numbers. The
action I describe below will take place with the input of as many as
60 different sets of numbers with up to 30 groups of numbers in each
set. There can be up to seven sets in the same column, separated by
headers. I will limit my question to the first set in the 1st column
for clarity.

The user puts a number into column A row 1, and then another number
into column B row 1. The spreadsheet simply adds the number in column
A row to the number in Column B Row 1 and displays it in column C row
1. (It actually does this many times in Row 1 alone in columns D, E
and F, etc., but again, I will limit the question to columns A, B and
C).

Then the user does the same thing in Row 2 entering a number into
column A and column B. The spreadsheet calculates column C. This
repeats for up to 30 rows.

If the calculated number in any row in Column C duplicates the
calulated number in another row in that same set in Column C, there is
a high probability that there was an error in data input - but not
necessarily! It is possible that they could be duplicates - just not
likely. I want to alert the person entering the data they they MAY
have made an error and should recheck their data entry. This should
be an almost instant automatic check without the user having to
manually initiate the check.

Using Data Validation and Conditional Formating, I could display an
error message with the option to accept it, and highlight the
conflicting cells if the user entered the data into the cell. This
would be perfect. But since Excel calculates the number, and will not
initiate data validation and condition formatting on a calculated
cell, I do not know how to make it happen.

Example

COLUMN A - COLUMN B COLUMN C
(Entered) (Entered) (Calculated)
Row 1 6 6 12
Row 2 7 5 12 - OOPS
- POSSIBLE ERROR IN A or B

I would like to display an error message that says "Your data entries
may be correct, but there is a high probability of a data entry error
- recheck your data" and an option to accept or reject the data and
possibly re-enter it if it was entered in error. If the user chooses
to accept it, I would like the warnings to disappear and not reappear
again, and for the program to move on. If I have to use VBA, I would
want it to somehow launch automatically when the data for the cells in
Column A and Column B is entered.

I have been reading and experimenting bit have not found a solution.
Can anyone help me with this one? I thank you in advance.

I'm thinking some extra, hidden helper cells on the rows would be one way to
deal with it. Given just 2 rows to work with and I'll use column D as the
helper column here.
No formula in D1 at all.
in D2:
=COUNTIF($C$1:C2,C2)
that will return a count of matches in column C above the current row for
the value in C on the current row (including itself). So, if no duplicates
above it, it will return a value of 1, but any duplicates above it will cause
it to return a value of 2 or more.

Back in C2, use Conditional Formatting with the condition being Formula Is
=D21
and set conditions for when D2 is greater than 1 to flag duplicates. Can't
help much with messages, but you can at least flag by color.


" wrote:

Here is my problem. I need to compare calculated data, and my
spreadsheet program will be used by unskilled people.

There are multiple sets of 3 columns each for input of numbers. The
action I describe below will take place with the input of as many as
60 different sets of numbers with up to 30 groups of numbers in each
set. There can be up to seven sets in the same column, separated by
headers. I will limit my question to the first set in the 1st column
for clarity.

The user puts a number into column A row 1, and then another number
into column B row 1. The spreadsheet simply adds the number in column
A row to the number in Column B Row 1 and displays it in column C row
1. (It actually does this many times in Row 1 alone in columns D, E
and F, etc., but again, I will limit the question to columns A, B and
C).

Then the user does the same thing in Row 2 entering a number into
column A and column B. The spreadsheet calculates column C. This
repeats for up to 30 rows.

If the calculated number in any row in Column C duplicates the
calulated number in another row in that same set in Column C, there is
a high probability that there was an error in data input - but not
necessarily! It is possible that they could be duplicates - just not
likely. I want to alert the person entering the data they they MAY
have made an error and should recheck their data entry. This should
be an almost instant automatic check without the user having to
manually initiate the check.

Using Data Validation and Conditional Formating, I could display an
error message with the option to accept it, and highlight the
conflicting cells if the user entered the data into the cell. This
would be perfect. But since Excel calculates the number, and will not
initiate data validation and condition formatting on a calculated
cell, I do not know how to make it happen.

Example

COLUMN A - COLUMN B COLUMN C
(Entered) (Entered) (Calculated)
Row 1 6 6 12
Row 2 7 5 12 - OOPS
- POSSIBLE ERROR IN A or B

I would like to display an error message that says "Your data entries
may be correct, but there is a high probability of a data entry error
- recheck your data" and an option to accept or reject the data and
possibly re-enter it if it was entered in error. If the user chooses
to accept it, I would like the warnings to disappear and not reappear
again, and for the program to move on. If I have to use VBA, I would
want it to somehow launch automatically when the data for the cells in
Column A and Column B is entered.

I have been reading and experimenting bit have not found a solution.
Can anyone help me with this one? I thank you in advance.

On Aug 31, 1:14 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:
I'm thinking some extra, hidden helper cells on the rows would be one way to
deal with it. Given just 2 rows to work with and I'll use column D as the
helper column here.
No formula in D1 at all.
in D2:
=COUNTIF($C$1:C2,C2)
that will return a count of matches in column C above the current row for
the value in C on the current row (including itself). So, if no duplicates
above it, it will return a value of 1, but any duplicates above it will cause
it to return a value of 2 or more.

Back in C2, use Conditional Formatting with the condition being Formula Is
=D21
and set conditions for when D2 is greater than 1 to flag duplicates. Can't
help much with messages, but you can at least flag by color.



" wrote:
Here is my problem. I need to compare calculated data, and my
spreadsheet program will be used by unskilled people.

There are multiple sets of 3 columns each for input of numbers. The
action I describe below will take place with the input of as many as
60 different sets of numbers with up to 30 groups of numbers in each
set. There can be up to seven sets in the same column, separated by
headers. I will limit my question to the first set in the 1st column
for clarity.

The user puts a number into column A row 1, and then another number
into column B row 1. The spreadsheet simply adds the number in column
A row to the number in Column B Row 1 and displays it in column C row
1. (It actually does this many times in Row 1 alone in columns D, E
and F, etc., but again, I will limit the question to columns A, B and
C).

Then the user does the same thing in Row 2 entering a number into
column A and column B. The spreadsheet calculates column C. This
repeats for up to 30 rows.

If the calculated number in any row in Column C duplicates the
calulated number in another row in that same set in Column C, there is
a high probability that there was an error in data input - but not
necessarily! It is possible that they could be duplicates - just not
likely. I want to alert the person entering the data they they MAY
have made an error and should recheck their data entry. This should
be an almost instant automatic check without the user having to
manually initiate the check.

Using Data Validation and Conditional Formating, I could display an
error message with the option to accept it, and highlight the
conflicting cells if the user entered the data into the cell. This
would be perfect. But since Excel calculates the number, and will not
initiate data validation and condition formatting on a calculated
cell, I do not know how to make it happen.

Example

COLUMN A - COLUMN B COLUMN C
(Entered) (Entered) (Calculated)
Row 1 6 6 12
Row 2 7 5 12 - OOPS
- POSSIBLE ERROR IN A or B

I would like to display an error message that says "Your data entries
may be correct, but there is a high probability of a data entry error
- recheck your data" and an option to accept or reject the data and
possibly re-enter it if it was entered in error. If the user chooses
to accept it, I would like the warnings to disappear and not reappear
again, and for the program to move on. If I have to use VBA, I would
want it to somehow launch automatically when the data for the cells in
Column A and Column B is entered.

I have been reading and experimenting bit have not found a solution.
Can anyone help me with this one? I thank you in advance.- Hide quoted text -

- Show quoted text -

That is brilliant. Thanks. I am half way there, and have not given
up on the other half!