Find cell value on one worksheet in another worksheet

[KTB23]

I have a list of people's names in one column on a worksheet called "Main". I also have 2 other worksheets, called "OpenIssues" & "ClosedIssues". I am looking for a way where I can start with the first name in the sheet "Main" and find where it is in the "OpenIssues" or in the "ClosedIssues", then provide either the word "OPEN" or "CLOSED" in column A next to the person's name, depending on which sheet the name is located in, and then onto the next name in the "Main" worksheet (down the entire column). Then, if possible, I would like to be able to link from the "OPEN" or "CLOSED" in column A to the location in either the "OpenIssues" list or the "ClosedIssues" list to where that name can be found. I have tried a million different ways in VBA to do this, however, I am not very familiar with VBA code, and can't get it to work just right. Any help in doing this would be GREATLY appreciated! THANKS!

 

[SkipVought]

Hi,

2 formulas
assumptions:
names in column A row 1
Columns B & C empty

So in col B

=if(ISERROR(MATCH(A1,OpenNameRange,0)),"","OPEN")

So in col C

=if(ISERROR(MATCH(A1,ClosedNameRange,0)),"","CLOSED")

Copy these formulas down

VOLA!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[KTB23]

OK, that is SO much easier than what I was trying to do! Thanks! Any idea on how to get the "OPEN" or "CLOSED" to link to that person's name on the other worksheet where it is located?

 

[SkipVought]

This will give you the relative row within the name range on the other sheet. If the name data starts in row 1 then it is the row number...

=if(ISERROR(MATCH(A1,OpenNameRange,0)),"",MATCH(A1,ClosedNameRange,0))
=if(ISERROR(MATCH(A1,ClosedNameRange,0)),"",MATCH(A1,ClosedNameRange,0))

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[KTB23]

OK, this works, but is there anyway to be able to click on the "OPEN" or "CLOSED" in column B or C where it will then automatically take me to the row on the other worksheet where the name is listed?

 

[SkipVought]

Enter this code in the sheet object containing you list of names with open and closed. To find the sheet object, right-click the sheet tab and select view code...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Select Case UCase(Target.Value)
    Case "OPEN"
        r = Application.Match(Cells(Target.Row, 1).Value, [OpenNameRange], 0)
        Range("OpenNameRange")(r, 1).Select
    Case "CLOSED"
        r = Application.Match(Cells(Target.Row, 1).Value, [ClosedNameRange], 0)
        Range("ClosedNameRange")(r, 1).Select
    End Select
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

Here is what will work.

1 in a separate module, paste this code...

Sub SelectName(rng As Range)
    On Error Resume Next
    Select Case UCase(rng.Value)
    Case "OPEN"
        r = Application.Match(Cells(rng.Row, 1).Value, [OpenNameRange], 0)
        Sheets("OpenIssues").Activate
        Range("OpenNameRange")(r, 1).Select
    Case "CLOSED"
        r = Application.Match(Cells(rng.Row, 1).Value, [ClosedNameRange], 0)
        Range("ClosedNameRange")(r, 1).Select
    End Select
End Sub

In the Sheet object paste this code...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    SelectName Target
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[KTB23]

This is still only bringing me to the general worksheet where it should be, but not to the row of the person?

 

[SkipVought]

oops,

Forgot that both cases need to Activate sheet before selecting.

Sub SelectName(rng As Range)
    On Error Resume Next
    Select Case UCase(rng.Value)
    Case "OPEN"
        r = Application.Match(Cells(rng.Row, 1).Value, [OpenNameRange], 0)
        Sheets("OpenIssues").Activate
        Range("OpenNameRange")(r, 1).Select
    Case "CLOSED"
        r = Application.Match(Cells(rng.Row, 1).Value, [ClosedNameRange], 0)
        Sheets("ClosedIssues").Activate
        Range("ClosedNameRange")(r, 1).Select
    End Select
End Sub

Some other things to check...

1. In the Sheet Object that has your Open & Closed formulas, do you have

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    SelectName Target
End Sub

2. In a module, do you have

Sub SelectName(rng As Range)
    On Error Resume Next
    Select Case UCase(rng.Value)
    Case "OPEN"
        r = Application.Match(Cells(rng.Row, 1).Value, [OpenNameRange], 0)
        Sheets("OpenIssues").Activate
        Range("OpenNameRange")(r, 1).Select
    Case "CLOSED"
        r = Application.Match(Cells(rng.Row, 1).Value, [ClosedNameRange], 0)
        Sheets("ClosedIssues").Activate
        Range("ClosedNameRange")(r, 1).Select
    End Select
End Sub

???



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[KTB23]

Here is exactly what I have:
Module1:

Sub ChooseName(rng As Range)

On Error Resume Next
Select Case UCase(rng.Value)
Case "OPEN"
r = Application.Match(Cells(rng.Row, 7).Value, [OpenNameRange], 0)
Sheets("OpenIssues&quot.Activate
Range("OpenNameRange&quot(r, 3).Select
Case "CLOSED"
r = Application.Match(Cells(rng.Row, 7).Value, [ClosedNameRange], 0)
Sheets("ClosedList&quot.Activate
Range("ClosedNameRange&quot(r, 3).Select
Case "OTHER"
r = Application.Match(Cells(rng.Row, 7).Value, [OtherRange], 0)
Sheets("OTHER&quot.Activate
Range("OtherRange&quot(r, 3).Select
Case "LABELS"
r = Application.Match(Cells(rng.Row, 7).Value, [LabelsRange], 0)
Sheets("LabelsSent&quot.Activate
Range("LabelsRange&quot(r, 3).Select
End Select
End Sub

(I changed the name to ChooseName because it gave an "ambiguous name" error)

Where 7 is the column location of the name on the "Main" sheet and 3 is the column location of name in the other worksheets? (Not sure if this part is accurate)


Sheet1(MAIN)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ChooseName Target
End Sub

 

[SkipVought]

Lets get an understanding.

1. Each of the ranges...
Range("OpenNameRange&quot
Range("ClosedNameRange&quot
Range("OtherRange&quot
Range("LabelsRange&quot

should ALL refer to the specific COLUMN of names on each sheet -- THERFORE the offset should be

(r, 1)

The easiest way to name a table range (meaning that the first row is headings and all the data is both contiguous and isolated from other tables) is...
1. select the current region (there is toolbar icon for this)
2. menu item Insert/Name/Create/Create Names in top row

and the use that heading-generated range name

I reconstructed it and it works for me

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[KTB23]

But if on my spreadsheet, the names for all of those ranges are in column C, than every instance should be (r, 3), right?

I guess I'm just all confused now...

 

[SkipVought]

That your name ranges are in column 3 on each seet, I could care less. The important thing is the named ranges
Range("OpenNameRange&quot
Range("ClosedNameRange&quot
Range("OtherRange&quot
Range("LabelsRange&quot

If, for instance, OpenNameRange is in column 3, then

Range("OpenNameRange")(1,3)

refers to the first relative row of data (which, if you have a heading is ROW 2) and the 3rd relative column of data (which would be out in left field in column 6 having NOTHING at all to do with OpenNameRange)

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com