Creating Dynamic Hyperlinks based on known cell value

[scaffdog845]

Morning Gang

Attached is a sample workbook using Hyperlinks to navigate easily between locations in the Workbook. The links on 'CU-7'! can remain static as the sheets they point to will not change in anyway to affect the link.

The links at A1 on '07-E-1A'! and '07-E-1B'! are currently static and pointing back to a specific cell on 'CU-7'! These are the Hyperlinks that would need to be dynamic. It is very common to add/delete multiple rows on 'CU-7'! The goal would be to have the Hyperlink point to the cell in 'CU-7'!A:A containg the value from $B$2

Notes in the workbook are in RED text.

Thanks for the help.

[Whizbang]

Take a look at this thread. Does it do as you want?

http://www.excelforum.com/excel-gene...html?p=2638625

[JBeaucaire]

1) Remove the Hyperlink built into your cell A1.
2) Put this formula in A1 instead:

=HYPERLINK("#'CU-7'!" & ADDRESS(MATCH($B2, 'CU-7'!$A:$A, 0), 1), "Turnaround History")

[JBeaucaire]

the same thing will work on the CU-7 sheet, too. Remove the hardcoded hyperlinks in column T, then put this formula in T4:

=HYPERLINK("#'" & LEFT(A4, FIND(",",A4)-1) & "'!B2", "Click to View History")

[scaffdog845]

It does Whizbang! Thanks for the link.

[scaffdog845]

JBeaucaire. This worked as well as the other responce and it was a simpler solution fo rthe user who would be dealing with it. Thanks.