Disallow duplicates in different worksheets

[Guest]

My co-worker wanted to avoid entering duplicates in her Excel 2002 document.
I was very proud of myself for discovering Data Validation, and that worked
fine. Now the problem is harder:

She has one workbook and every day she adds a new worksheet to it. She
wants to make sure the data she's entering wasn't also entered on a previous
day. This is beyond me. Any ideas?

 

[Guest]

You could look at something like this

http://www.exceltip.com/st/Compare_two_worksheets_using_VBA_in_Microsoft_Excel/477.html

which is how to compare 2 worksheets but if you could be more specific in
you question then a more specific answer is likely.

Mike

 

[Guest]

Thanks, Mike.

She's entering client numbers, with lots of other data in each row (address,
amount paid, etc.).
When she enters a client number she's already entered that day, I've set
Data Validation to give her an error message ("Hey, goof, you've already
entered this one!") which stops her from entering it a second time.

But each day she starts fresh with a new Worksheet in her Workbook. If a
client came in Monday and Wednesday, she wants to be stopped from entering
him a second time on Wednesday. Hope this is clear. I'd like her to be
stopped as she's entering the data, not comparing lists after the fact. Hope
this is clear.

 

[Guest]

Now i understand. try this:-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
Set MyRange = Worksheets("monday").Range("A1:A100")
If Intersect(Target, Range("A1:A100")) Is Nothing Then Exit Sub
For Each c In MyRange
If c.Value = Target.Value Then
MsgBox ("You entered " & Target.Value & " Previously")
End If
Next
End Sub

As you will note it minitors the range a1 a100 and when a cell changes it
checks if the same data are in A1 - a100 i=on a sheet called Monday. You will
need to expand it for other days of the week.

Mike

 

[Earl Kiosterud]

ClaireView,

Take a look at "Data across multiple sheets" at
http://www.smokeylake.com/excel/excel_truths.htm. Don't be too quick to dismiss it -- it
might pay off in the long run to keep the data consolidated.
--
Earl Kiosterud
www.smokeylake.com

Note: Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...

 

[Roxy]

Caireview, did you ever find an answer to your question? I am having the same
issue with my spreadsheets and I honestly didn't quite understand the answer
you recieved from Mike. Can you help? Thanks- Roxy