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
> =D2>1
> 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.
>
>
>
> "RJQ...@gmail.com" 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!
|
Similar Threads
