Moving cursor to identical cells across multiple sheets

[Guest]

This post is a follow up query to John Dawson's post of 10/28/2005, in which
he asked how to move the cursor from an individual item (eg one of a list of
football teams, arranged in alphabetical order) in a column of Sheet 1, to
the same item in a similar column on the next sheet. However, in the second
sheet the items are in a different order (eg this might be the league
positions of the teams on the first sheet).

Stef vey kindly provided the following macro which did the job perfectly:

Sub Teamfind()
Dim keresrng As Range
csapat = ActiveCell.Value
Sheets("Position").Select <== Change Position to the actual name of
2.sheet!
Set keresrng = Range("A2:A65536")
Rows(WorksheetFunction.Match(csapat, keresrng, 0) + 1).Select
End Sub

My question now is this... Imagine that ALL the teams are still listed on
the first sheet. However I need to move the cursor to the identical team
which is listed on any one of the following THREE sheets. Following the
above example, these could be three different football leagues. It is not
known which of the three sheets it is listed on, but it is only listed once
on one of the three sheets.

Can anybody help, please?

Yours,
Sue Sch

 

[Jan Karel Pieterse]

Hi Sue,

This post is a follow up query to John Dawson's post of 10/28/2005, in which
he asked how to move the cursor from an individual item (eg one of a list of
football teams, arranged in alphabetical order) in a column of Sheet 1, to
the same item in a similar column on the next sheet. However, in the second
sheet the items are in a different order (eg this might be the league
positions of the teams on the first sheet).

Would my Flexfind utility be of help:

www.jkp-ads.com/OfficeMarketPlaceFF-EN.htm

When invoked whilst on a cell with a team name, Flexfind offers that name to
search for and it will find that name on any worksheet in your workbook(s).

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

 

[Guest]

Dear Jan,
Thanks for your prompt suggestion. However I was really looking for
something much simpler which could be copied into a macro. Also, our
security system does not allow us to download utilities.

One possible solution I wondered about, was that if I listed after each team
name on Sheet 1 upon which one of the following three sheets the match would
be found, could I then move to the appropriate sheet before starting up
Stef's part of the macro.

Any further assistance would be much appreciated.
Yours,
Sue Sch

 

[Jan Karel Pieterse]

Hi Sue,

One possible solution I wondered about, was that if I listed after each team
name on Sheet 1 upon which one of the following three sheets the match would
be found, could I then move to the appropriate sheet before starting up
Stef's part of the macro.

You could try without a macro (if there are not too many this should work OK).

Suppose the names are on the worksheets Sheet2, Sheet3 and Sheet4 (and you're
in worksheet Sheet1), on each worksheet in the range $A$2:$A$100.

This formula will create a direct, dynamic link the first worksheet (and cell)
that name (that is in cell A2 ) is found in:

=HYPERLINK(IF(NOT(ISERROR(MATCH(A2,Sheet2!$A$2:$A$100,0))),"#"&ADDRESS(MATCH(A2
,Sheet2!$A$2:$A$100,0)+1,1,,,"Sheet2"),IF(NOT(ISERROR(MATCH(A2,Sheet3!$A$2:$A$1
00,0))),"#"&ADDRESS(MATCH(A2,Sheet3!$A$2:$A$100,0)+1,1,,,"Sheet3"),IF(NOT(ISERR
OR(MATCH(A2,Sheet4!$A$2:$A$100,0))),"#"&ADDRESS(MATCH(A2,Sheet4!$A$2:$A$100,0)+
1,1,,,"Sheet4"),"Not Found"))))

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

 

[Guest]

Jan,
Thanks very much. This works very well, and I learnt a new function
(ISERROR) along the way!
Sue