Is this possible?

[RickeyS]

I have workbook with five work sheets,

Sheet 1 has a list of names in column A (about 1500 names)

Sheets 2 thru sheet 5 will have some of the names from sheet 1 on them (always in column A)

As I use a name from sheet 1 (put it on sheet 2 thru 5) I would like the following to happen,
first somehow see that the name was used on sheet 1(strikethru, color change font, fill, etc)

Second if by chance a name is put on sheet 2 thru 5 twice again have some way to tell.

So sheet 1 being a master list of names that can be used on the other sheets in which someone would be able to tell by looking at sheet 1 if the name was used and on the other four sheets be able to tell if a name has been used more than once on those four sheets.

I just don't know where to start.
Thanks for all your help

 

[Erick]

Just taking a stab at what you are trying to do....
For your Sheet1, if you want to do some custom formatting on items that appear on the other sheets, you can do Conditional formatting and use a nested vlookup to see if each name in Sheet1 is in the other sheets.

The only catch is that as far as I know, you cannot refer to other sheets when writing a condition formatting formula (I am running Excel2000 and I don't know if this is still the case of the newer version). So for this to work, you will have to reference the entries of the other sheets in Sheet1. So maybe in Sheet1.B1 = Sheet2.A1, Sheet1.C1 = Sheet3.A1, etc. Then as a conditional formatting formula you can have:

=OR(NOT(ISNA(VLOOKUP(A1,B:B,1,0))),NOT(ISNA(VLOOKUP(A1,C:C,1,0))),NOT(ISNA(VLOOKUP(A1,D:D,1,0))),NOT(ISNA(VLOOKUP(A1,E:E,1,0))))

for the other sheets, you can do a countif formula, so for Sheet2.A1, you can have a formula on say, B1:

[FONT=Arial]=COUNTIF(Sheet3!A:A,A1)+COUNTIF(Sheet4!A:A,A1)+COUNTIF(Sheet5!A:A,A1)[/FONT]

 

[stnkynts]

Another option:

Option Explicit
Sub Macro1()
Dim ws1 As Worksheet:   Set ws1 = Sheets("Sheet1")
Dim iFind As Range
Dim icell As Long, wksht As Long
 
For icell = 1 To ws1.Range("A" & Rows.Count).End(xlUp).Row
    For wksht = 2 To Worksheets.Count
    Set iFind = Sheets(wksht).Range("A:A").Find(what:=ws1.Range("A" & icell).Value, LookIn:=xlValues, LookAt:=xlWhole)
    If Not iFind Is Nothing Then
        ws1.Range("A" & icell).Font.Strikethrough = True
        ws1.Range("B" & icell).Value = ws1.Range("B" & icell).Value + 1
    End If
    Next wksht
Next icell
 
End Sub

Its set to strikethrough names on Sheet 1 that are on sheets 2 through # of worksheets. To count the number of sheets that the name is on i set it to put a value in column B of the corresponding cell on sheet 1.

Note: It is not set to count the total number of times that persons name comes up just the number of sheets that persons name is on.