Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I check for duplicates across multiple worksheets | Excel Worksheet Functions | |||
Finding duplicates in 2 worksheets | Excel Worksheet Functions | |||
Comparing two worksheets with two columns each for duplicates | Excel Discussion (Misc queries) | |||
excel duplicates on 2 worksheets | Excel Discussion (Misc queries) | |||
Disallow cell entries | Excel Discussion (Misc queries) |