Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 21
Default Disallow duplicates in different worksheets

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?
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default Disallow duplicates in different worksheets

You could look at something like this

http://www.exceltip.com/st/Compare_t...ce l/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


"ClaireView" wrote:

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?

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 21
Default Disallow duplicates in different worksheets

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.

"Mike H" wrote:

You could look at something like this

http://www.exceltip.com/st/Compare_t...ce l/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


"ClaireView" wrote:

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?

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default Disallow duplicates in different worksheets

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

"ClaireView" wrote:

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.

"Mike H" wrote:

You could look at something like this

http://www.exceltip.com/st/Compare_t...ce l/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


"ClaireView" wrote:

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?

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 611
Default Disallow duplicates in different worksheets

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...
-----------------------------------------------------------------------
"ClaireView" wrote in message
...
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?





  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 45
Default Disallow duplicates in different worksheets

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

"ClaireView" wrote:

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?

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I check for duplicates across multiple worksheets Chrissy Excel Worksheet Functions 2 July 29th 06 06:34 PM
Finding duplicates in 2 worksheets darkwood Excel Worksheet Functions 2 May 12th 06 08:06 PM
Comparing two worksheets with two columns each for duplicates jlcnewyork Excel Discussion (Misc queries) 0 February 15th 06 03:32 PM
excel duplicates on 2 worksheets Luke Rogers Excel Discussion (Misc queries) 3 February 6th 06 05:31 PM
Disallow cell entries hopeace Excel Discussion (Misc queries) 3 October 14th 05 12:19 AM


All times are GMT +1. The time now is 11:13 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"