Here is some amended code.

I have tried to indicate where and how you wouldextend it for all of your
ranges

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE_11 As String = "H1:H43" '<== change to suit
Const WS_RANGE_12 As String = "I1:I43" '<== change to suit
Const WS_RANGE_13 As String = "J1:J43" '<== change to suit
Const WS_RANGE_21 As String = "H52:H81" '<== change to suit
Const WS_RANGE_22 As String = "I52:I81" '<== change to suit
Const WS_RANGE_23 As String = "J52:J81" '<== change to suit
'etc.
Const WS_RANGE_81 As String = "K14:K43" '<== change to suit
Const WS_RANGE_82 As String = "L14:L43" '<== change to suit
Const WS_RANGE_83 As String = "M14:M43" '<== change to suit
'... add other ranges in groups sof 3 as above ... and ...
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

On Error GoTo ws_exit
Application.EnableEvents = False

Select Case True
Case Not Intersect(Target, Me.Range(WS_RANGE_11)) Is Nothing Or _
Not Intersect(Target, Me.Range(WS_RANGE_12)) Is Nothing
Call CheckUsed(Target, _
Me.Range(WS_RANGE_11).Column, _
Me.Range(WS_RANGE_12).Column, _
Me.Range(WS_RANGE_13).Column)
Case Not Intersect(Target, Me.Range(WS_RANGE_21)) Is Nothing Or _
Not Intersect(Target, Me.Range(WS_RANGE_22)) Is Nothing
Call CheckUsed(Target, _
Me.Range(WS_RANGE_21).Column, _
Me.Range(WS_RANGE_22).Column, _
Me.Range(WS_RANGE_23).Column)
Case Not Intersect(Target, Me.Range(WS_RANGE_81)) Is Nothing Or _
Not Intersect(Target, Me.Range(WS_RANGE_82)) Is Nothing
Call CheckUsed(Target, _
Me.Range(WS_RANGE_81).Column, _
Me.Range(WS_RANGE_82).Column, _
Me.Range(WS_RANGE_83).Column)
'add other case statements for other 3 range groups
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
End Select

ws_exit:
Application.EnableEvents = True
End Sub


Private Sub CheckUsed(ByVal Target As Range, ByVal Col1 As Long, ByVal Col2
As Long, ByVal Col3 As Long)
Dim cellLink As Boolean
With Target
If Application.CountIf( _
Me.Columns(Col3), Me.Cells(.Row, Col1).Value + _
Me.Cells(.Row, Col2).Value) = 1 Then
ActiveWorkbook.Names.Add Name:="_cell_" & .Address, _
RefersTo:=True
Else
On Error Resume Next
cellLink = Me.Evaluate( _
ActiveWorkbook.Names("_cell_" & .Address(0, 0)).RefersTo)
On Error GoTo 0
If cellLink < True Then
If MsgBox("Sum already used, accept anyway?", _
vbYesNo + vbQuestion) = vbYes Then
ActiveWorkbook.Names.Add _
Name:="_cell_" & Me.Cells(.Row, Col1).Address(0, 0),
_
RefersTo:=True
ActiveWorkbook.Names.Add _
Name:="_cell_" & Me.Cells(.Row, Col2).Address(0, 0),
_
RefersTo:=True
Else
.Value = ""
End If
End If
End If
End With
End Sub



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



wrote in message
ups.com...
On Sep 17, 8:19 am, "Bob Phillips" wrote:
I would use Worksheet Change event.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A:B" '<== change to suit
Dim cellLink As Boolean

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Application.CountIf(Me.Columns(3), Me.Cells(.Row,
"A").Value
+ _
Me.Cells(.Row, "B").Value) = 1 Then
ActiveWorkbook.Names.Add Name:="_cell_" & .Address,
RefersTo:=True
Else
On Error Resume Next
cellLink = Me.Evaluate(ActiveWorkbook.Names("_cell_" &
.Address(0, 0)).RefersTo)
On Error GoTo ws_exit
If cellLink < True Then
If MsgBox("Sum already used, accept anyway?", vbYesNo
+
vbQuestion) = vbYes Then
ActiveWorkbook.Names.Add Name:="_cell_" &
.Address(0, 0), RefersTo:=True
Else
.Value = ""
End If
End If
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

wrote in message

ups.com...



I am struggling with the following problem.

I have multiple groups of numerical information. Each group has 3
columns - which I will call column A, column B and column C. The user
inputs column A and column B - column C simply adds column A and
column B. If the result of adding column A and column B is the same
as a previous result in that same group, then I want to alert the user
that the data input may be incorrect. It could be correct, but they
should double-check to be certain, as it is probably incorrect.

If the result of adding the two numbers created a duplicate in column
C, the message would state something like "CAUTION - you may have
entered the correct data, but double-check" and then give the option
to accept the data or make the correction, as in conventional data
validation.

I need the solution to be in real time, so I think VBA is out, as I
want the entry of the data to trigger the caution note within a few
seconds, and I do not know a way to trigger a Macro that is column-
specific. Also, once the data involved in a specific entry has been
examined and found to be OK, I do not want to force the user to re-
examine that data again.

Data validation would work perfectly for this problem if it could be
used on cells that are calculated, but of course, it cannot. I know
logically that there is a way to use data validation to do this by
examining the data calculated thus far, and then comparing this to the
entry the user makes in column A and alerting him if the entry he
makes in column B would cause an answer in column C that already
exists. But the programming to do this by brute force would be
extensive, if I could even figure it out and I am not sure, but
perhaps there are even internal limits in Excel that would prevent
adding up to 30 variables to the list to search from. Since I will
have about 90 lists, each with 30 sets of data, on a given
spreadsheet, it seems that brute force, while it might work, may be
hugely memory intensive.

However, since there is a logical answer, there must be a progamming
answer?? I just do not have enough knowledge to figure it out. Can
anyone help me?

Here is a brief example of how brute force might work logically;

A B C

2 7 9 (User enters the 2 and the 7 - Excel calculates 2
+ 7 = 9
3 (Progam now calculates that adding a 6 in
column B would create a duplicate in column C, so validation would
display a message if and only if a 6 was added in column B).
Say the user added a 5 - then we would have

A B C

2 7 9
3 5 8
4 (Program now calculates that adding a 4 in coluimn
B would create a duplicate (8) or adding a 5 in column B would also
create a duplicate in column C (9), so validation would display a
message if a 4 or 5 were added in column B).

I may be able to reduce the lists from 30 sets of data to around 10
sets of data, as that would work for most users - the 30 sets of data,
however, cover every possible need, if that would help.

Is there someone that can guide me in an efficient way to solve this
problem? I thank you very much in advance...- Hide quoted text -

- Show quoted text -

Hello - this works great - but I really do not understand the code
well enough to adapt it to the multiple groups and multiple
columns... How would I do that? For example;

Group 1

Range for 1st column is H14 - H43; range for 2nd column is I14 - I43,
and Range for 3rd column is J14 - J43

Group 2

Range for 1st column is H52-H81, 2nd column I52-I81, and 3rd column
J52-J81

Group 3 - 7 Same concept in same columns

Then we move to Group 8, which stacks 7 more groups in the next set of
columns

Group 8 1st column K14-K43, 2nd column L14 - L43, 3rd column M14 M43
and so forth...

There are 15 columns sets, and each column set has 7 groups in the
column. To complicate it even more, three column sets have 3 sets of
input data added together.

I am only concerned with comparing data WITHIN each group - not
external to the group. There are 105 groups in total, I think...

Sorry that my knowledge base is not deeper, but it is a shallow pool,
I am afraid. What would I have to do to make this work on all of
these groups?

I really very much appreciate the help.

Also, I thank Mr. Peterson. However, due to the construction of the
sheet, I do not have the luxury of an extra column - the sheet has to
be printed out later, and it barely fits the existing columns without
adding a blank column for the message...