I hate excel and things got worse when my manager told me to add hyperlinks on an excel sheet where each cell will link to another cell in a different sheet in the same excel sheet.
I started doing it manually and figured out that it would take me half day to just manually do this. There was around 1000cells to be taken care of and then when i figured out this formula.
I am going to save this in this blog as am sure i am going to need this somethime in my life again.
=HYPERLINK(“#'SHEET_TO_MATCH'!" & ADDRESS(MATCH($K10, SHEET_TO_MATCH!A:A, 0), 1), "Click")
This is how it works.
We use 3 functions here.
- First we find a match with the current cell using MATCH function.
- 2nd if match found we get its address using ADDRESS function.
- With the address we create a hyperlink using the HYPERLINK function.
- $K10 is the current cell for which i am trying to find a match
- A:A is the list column in the target sheet where the match can be found.
- “Click” is the hyperlink name.
Hope that helps.
I still hate Excel.