hyperlink doesn't work after ...

[Tom2020]

Hello all

I have an excel file with two sheets (sheetA, sheetB). From sheetA, A10, I have created an anchor to sheetB, E14. The value of both cells in two sheets is the same, a string like “FI_2005_first_05.pdf”.
Now I inserted a hyperlink from sheetA to E14 in the sheetB. If I click on, I will jump to E14 .... nothing special about that.

Now, imagine, I sort column, delete or add some rows to the sheetB, my hyperlink wouldn’t work at all.

Any Idea how to fix this issues? I would like to have some code(VBA) or combination formula to change >10000 anchors.

Thank you for helping me

tom

 

[RasGhul]

Maybe you could use this? I've left just the cell("address" formula in some cells to show how this returns the cell position back into hyperlink.

Book1
	A	B	C	D	E
2	FI_2005_first_05.pdf				FI_2005_first_05.pdf
3	FI_2005_first_06.pdf				[Book1]Sheet2!$A$8
4	FI_2005_first_07.pdf				[Book1]Sheet2!$A$7
5	FI_2005_first_08.pdf				[Book1]Sheet2!$A$6
6	FI_2005_first_09.pdf				[Book1]Sheet2!$A$5
7	FI_2005_first_10.pdf				[Book1]Sheet2!$A$4
8	FI_2005_first_11.pdf				[Book1]Sheet2!$A$3
Sheet1

Cell Formulas
Range	Formula
E2	E2	=HYPERLINK("#"&CELL("address",INDEX(Sheet2!$A$2:$A$9,MATCH(Sheet1!A2,Sheet2!$A$2:$A$9,0))),A2)
E3:E8	E3	=CELL("address",INDEX(Sheet2!$A$2:$A$9,MATCH(Sheet1!A3,Sheet2!$A$2:$A$9,0)))

Book1
	A
1	Names
2	FI_2005_first_12.pdf
3	FI_2005_first_11.pdf
4	FI_2005_first_10.pdf
5	FI_2005_first_09.pdf
6	FI_2005_first_08.pdf
7	FI_2005_first_07.pdf
8	FI_2005_first_06.pdf
9	FI_2005_first_05.pdf
Sheet2

 

[Tom2020]

I’m very happy and satisfied with … thank you so much.

Bytheway, as already mentioned it is more than 10k hyperlinks to change, I’m wondering whether it is possible to run dynamically a VBA macro for these changes!

thx

 

[RasGhul]

Hi Tom,

There's probably more efficient code than this, but this Clears & Refreshes the hyperlinks for the whole of Column E from Sheet1E2

Please try on a test copy before applying to your real sheet

Dim rec As Range

Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

Set rec = Sheets("Sheet1").Range("E:E")
    rec.Select
        rec.ClearContents

        Sheets("Sheet1").Range("E2").Select
        ActiveCell.Formula2R1C1 = _
            "=IFNA(HYPERLINK(""#""&CELL(""address"",INDEX(Sheet2!R2C1:R9C1,MATCH(Sheet1!RC[-4],Sheet2!R2C1:R9C1,0))),RC[-4]),"""")"
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Formula2R1C1 = _
            "=IFNA(HYPERLINK(""#""&CELL(""address"",INDEX(Sheet2!R2C1:R9C1,MATCH(Sheet1!RC[-4],Sheet2!R2C1:R9C1,0))),RC[-4]),"""")"
        Columns("E:E").EntireColumn.AutoFit
        Sheets("Sheet1").Range("a1").Select
    
    Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

 

[Tom2020]

many thanks.

with little problem, it works! ….

However, me and my manager were very frightened to make any decision. They are a lot of files.

… But, we have decided to leave the user with a possibility, especially if the anchor failed to show correct file name. The idea is to set this function (below) in an if-else-statement with Application.inputbox:

=HYPERLINK("#"&CELL("address",INDEX(Sheet2!$A$2:$A$9,MATCH(Sheet1!A2,Sheet2!$A$2:$A$9,0))),A2)

If “user click Cancel button of Application.inputbox, it exits”

Else “allow user to select a cell (e,g, A2) with an Application.inputbox to set anchor again!”

t