Quickly create multiple unique hyperlinks to multiple unique locations in the same workbook

Question

Hi,

I am currently trying to hyperlink multiple cells on one worksheet in a workbook, with another set of cells in a separate worksheet in the same workbook (Excel 2010). 

Worksheet A has a list of company names in column A and various types of info (text) in the subsequent columns. Worksheet B has exactly the same list of company names in column A but different types of information (numeric) in the subsequent columns. I want to be able to quickly move between the two sets of info by hyperlinking the company names (e.g. Company 1 on Worksheet A to Company 1 on Worksheet B and vice versa) . I've done this manually for the first few, but there are over 350 company names (which means I'd have to individually hyperlink 700 times). I've tried a number of other methods to quickly hyperlink the cells but they're just not working. This includes trying to use the excel hyperlink function but it doesn't appear to work if it isn't a file you're trying to hyperlink to. I've also searched and tested multiple suggestions from websites and forums but none work exactly for my situation.

Any assistance will be appreciated.

Regards,

Ryan

Answer 1

Re:  "This includes trying to use the excel hyperlink function but it doesn't appear to work"

Kinda from the xl97 help file...

'---

Jump to a different cell in the same workbook.

You can create hyperlinks in a worksheet to jump from one cell to another cell in the same workbook.

For example, in the workbook Budget.xlsx, the following formula creates a hyperlink to cell E56 in the active worksheet.

The link text itself is the value in cell E56.

  =HYPERLINK("[Budget.xlsx]E56", E56)

  =HYPERLINK("[Budget.xlsx]September!E56", E56)

Kinda from Dave McRitchie...

'---

This version can be filled down and the cell address will increment (no quote marks)...

  =HYPERLINK("#"&CELL("address",Sheet2!A3),Sheet2!B3)

Note: be very attentive when writing your formula.

'---

Jim Cone

Portland, Oregon USA

https://goo.gl/IUQUN2 (Dropbox)

(free & commercial excel add-ins & workbooks)

Answer 2

Hey,

Yes, the following suggestion from the answer above worked for me,

This version can be filled down and the cell address will increment (no quote marks)...

  =HYPERLINK("#"&CELL("address",Sheet2!A3),Sheet2!B3)

Answer 3

Hey,

Were you able to figure out how to do it quickly rather then one by one?

Answer 4

Thank you very much! The last version worked absolutely perfectly for the problem I had.