Good day,

I was wondering if the data validation works in terms of duplicates
across multiple tabs. I.E If the user enters the Unique ID in column A
on Sheet1 but the SSN is already entered in column A on Sheet2, but I
just wanted a warning to Appear letting the user know it exists
elsewhere in the file. I assume I would have to do this in vba but not
really sure how I could start it.

Thanks

Chris


--
TheLeafs
------------------------------------------------------------------------
TheLeafs's Profile: http://www.excelforum.com/member.php...o&userid=10131
View this thread: http://www.excelforum.com/showthread...hreadid=545522

Right click on the sheet tab where the entry is being made and select view
code:

Private Sub Worksheet_Change(ByVal Target As Range)
If target.Column = 1 and Target.Row 2 then
With Worksheets("Sheet2")
res = Application.Countif(.Columns(1),Target.value)
if res 0 then
msgbox "This is a duplicate of an entry in Sheet2"
end if
End with
End if
End Sub

--
Regards,
Tom Ogilvy


"TheLeafs" wrote:


Good day,

I was wondering if the data validation works in terms of duplicates
across multiple tabs. I.E If the user enters the Unique ID in column A
on Sheet1 but the SSN is already entered in column A on Sheet2, but I
just wanted a warning to Appear letting the user know it exists
elsewhere in the file. I assume I would have to do this in vba but not
really sure how I could start it.

Thanks

Chris


--
TheLeafs
------------------------------------------------------------------------
TheLeafs's Profile: http://www.excelforum.com/member.php...o&userid=10131
View this thread: http://www.excelforum.com/showthread...hreadid=545522

Hi Tom,

Thank you for your reply, but I should have explained the entire
situation. I will have 30 Sheets/Tabs. I have to validate an Unique
Identifier when an entry is made in colomn A, including the sheet in
which it is being worked on. Do you know if this is possible?

Chris


--
TheLeafs
------------------------------------------------------------------------
TheLeafs's Profile: http://www.excelforum.com/member.php...o&userid=10131
View this thread: http://www.excelforum.com/showthread...hreadid=545522

Private Sub Worksheet_Change(ByVal Target As Range)
If target.Column = 1 and Target.Row 2 then
for each sh in Worksheets
res = Application.Countif(sh.Columns(1),Target.value)
if res 0 then
msgbox "This is a duplicate of an entry in " & sh.Name
exit sub
end if
Next
End if
End Sub

--

Regards,
Tom Ogilvy


"TheLeafs" wrote:


Hi Tom,

Thank you for your reply, but I should have explained the entire
situation. I will have 30 Sheets/Tabs. I have to validate an Unique
Identifier when an entry is made in colomn A, including the sheet in
which it is being worked on. Do you know if this is possible?

Chris


--
TheLeafs
------------------------------------------------------------------------
TheLeafs's Profile: http://www.excelforum.com/member.php...o&userid=10131
View this thread: http://www.excelforum.com/showthread...hreadid=545522

Thanks Tom, would I have to put this code in every sheet correct?

Chris


--
TheLeafs
------------------------------------------------------------------------
TheLeafs's Profile: http://www.excelforum.com/member.php...o&userid=10131
View this thread: http://www.excelforum.com/showthread...hreadid=545522

that would be one option. Another would be to use the workbook level
equivalent event: (found in the ThisWorkbook module)

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If target.Column = 1 and Target.Row 2 then
for each sh1 in Worksheets
res = Application.Countif(sh1.Columns(1),Target.value)
if res 0 then
msgbox "This is a duplicate of an entry in " & sh1.Name
exit sub
end if
Next
End if

End Sub

--
Regards,
Tom Ogilvy


"TheLeafs" wrote:


Thanks Tom, would I have to put this code in every sheet correct?

Chris


--
TheLeafs
------------------------------------------------------------------------
TheLeafs's Profile: http://www.excelforum.com/member.php...o&userid=10131
View this thread: http://www.excelforum.com/showthread...hreadid=545522

Hi Tom,

Thank you for your reply, but I should have explained the entire
situation. I will have 30 Sheets/Tabs. I have to validate an Unique
Identifier when an entry is made in colomn A, including the sheet in
which it is being worked on. Do you know if this is possible?

Chris


--
TheLeafs
------------------------------------------------------------------------
TheLeafs's Profile: http://www.excelforum.com/member.php...o&userid=10131
View this thread: http://www.excelforum.com/showthread...hreadid=545522

Good day Tom,

It would be great if you could answer just have one more question i
you have time. Bascially what I was looking to do is have an share
excel file over a network that was going to be used by 22 people. Eac
person will be using there respective tab where they will be enterin
SSN's and other information. What I needed to happen is that i
PersonA entered an SSN and a few minutes later PersonB entered the sam
SSN then I wanted an message to appear warning the user of this. Bu
when I tested the file attached it only works for a single user. I.E I
I enter in the same SSN then the message appears. Do you know of a wa
or a solution to this, if you do it would be a great help.

Thank you for your tim

+-------------------------------------------------------------------
|Filename: Duplicate_Call_Test_Project.zip
|Download: http://www.excelforum.com/attachment.php?postid=4866
+-------------------------------------------------------------------

--
TheLeaf
-----------------------------------------------------------------------
TheLeafs's Profile: http://www.excelforum.com/member.php...fo&userid=1013
View this thread: http://www.excelforum.com/showthread.php?threadid=54552

Sorry about that I posted the wrong file. Attached is the correct one

+-------------------------------------------------------------------
|Filename: Duplicate_Call_Test_Project.zip
|Download: http://www.excelforum.com/attachment.php?postid=4867
+-------------------------------------------------------------------

--
TheLeaf
-----------------------------------------------------------------------
TheLeafs's Profile: http://www.excelforum.com/member.php...fo&userid=1013
View this thread: http://www.excelforum.com/showthread.php?threadid=54552