Question posted 2012 · +4 upvotes
I would like to know how to create a hyperlink from one excel sheet to another, automatically through a script, based on equal text values that both cells in their respective sheets have.
If this can be done without a script, using a formula of some kind (like VLOOKUP) this would be preferable.
thanks for your time.
Accepted answer +10 upvotes
- Hyperlink on same sheet using a value in A1:
=HYPERLINK(“#” & ADDRESS(MATCH(A1, B1:B5, 0), 2), “Link”)
- Hyperlink to a specific different sheet using a value in A1:
=HYPERLINK(“#’My Database’!” & ADDRESS(MATCH($A1, ‘My Database’!$A:$A, 0), 1), “Link”)
- Hyperlink to a sheet listed in cell A1
=HYPERLINK(“#'” & A1 & “‘!A1”, “Link”)
- Hyperlink to a random position in a column that must be found on a random sheet listed in cell C3, matching the value in A1, a 3D INDEX/MATCH/Hyperlink:
=HYPERLINK(“#” & CELL(“address”, INDEX(INDIRECT(C3 & “!A:A”), MATCH(A1, INDIRECT(C3 & “!A:A”), 0))), “Link”)
There’s a sample sheet found here where you can see these applied: 3D Hyperlink Examples
Top vba Q&A (6)
- Difference between Visual Basic 6.0 and VBA +122 (2009)
- VBA – how to conditionally skip a for loop iteration +116 (2011)
- VBA: Test if string begins with a string? +53 (2013)
- html parsing of cricinfo scorecards +47 (2012)
- Code to loop through all records in MS Access +46 (2011)
- Access VBA | How to replace parts of a string with another string +44 (2011)
vba solutions on this site
.