Find exact match, same workbook, different sheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a worksheet named, "contact master" that has the name and address of
employees.

I have a second worksheet named, "master schedule" that has the names and
training schedules of employees.

I want to click on the name of the employee in the "contact master" and have
it take me directly to that employee's name on the "master schedule".

Hyperlink only allows me to choose a cell reference, which changes whenever
I perform a sort.
 
Try this sample from my archives which illustrates a way:
http://www.savefile.com/files/1054222
Hyperlink item to table range below.xls

The sample uses this point formula to create the hyperlinks
=IF(A2="","",IF(ISNA(MATCH(A2,B$20:B$200,0)),"",HYPERLINK("#"&CELL("address",INDIRECT("B"&MATCH(A2,B$20:B$200,0)+19)),A2)))

Try adapting it to suit. It'll work to find the match in/jump to another
sheet, too. Gotta go now ..
 
Here's a link to a sample customized to your set-up:
http://cjoint.com/?jtdXOhbQQu
Hyperlink matching name in another sht.xls
(d/l and open the file from there)

In contact master,
Assuming names listed in A2:A10

In B2:
=IF(A2="","",IF(ISNA(MATCH(A2,'master
schedule'!A:A,0)),"",HYPERLINK("#"&CELL("address",INDIRECT("'master
schedule'!A"&MATCH(A2,'master schedule'!A:A,0))),A2)))
Copy down to B10. The above creates hyperlinks in col B which jumps to the
matched name in master schedule's col A, as desired. Names in master
schedule's col A (also assumed in A2 down) can be in any scrambled order
different from those listed in contact master.

---
 
Back
Top