Moving cursor to identical cells across multiple sheets
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 |
Moving cursor to identical cells across multiple sheets
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 |
Moving cursor to identical cells across multiple sheets
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" wrote: 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 |
Moving cursor to identical cells across multiple sheets
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(ISER ROR(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(MA TCH(A2,Sheet4!$A$2:$A$100,0)+ 1,1,,,"Sheet4"),"Not Found")))) Regards, Jan Karel Pieterse Excel MVP http://www.jkp-ads.com |
Moving cursor to identical cells across multiple sheets
Jan,
Thanks very much. This works very well, and I learnt a new function (ISERROR) along the way! Sue "Jan Karel Pieterse" wrote: 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(ISER ROR(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(MA TCH(A2,Sheet4!$A$2:$A$100,0)+ 1,1,,,"Sheet4"),"Not Found")))) Regards, Jan Karel Pieterse Excel MVP http://www.jkp-ads.com |
All times are GMT +1. The time now is 01:24 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com