dynamic hyperlink

[leecavturbo]

well when i say dynamic. I have a hyperlink ( link ? ) in sheet 2 to a cell in sheet 1. but what i need is when i copy that sheet to make another ( with tab, copy move to end ) is for the link to reference sheet 1 but 1 row lower than the last.
possible without vba ?

 

[leecavturbo]

No idea unfortunately Maybe you should wait for another help.

typo on my behalf. massive thanks !!!!!!!!!!!!!!!!!!!!!!!!!!!!! 1 year been trying to fix that

 

[Legacy 143009]

I hope this would help:

WeTransfer - Send Large Files & Share Photos Online - Up to 2GB Free

WeTransfer is the simplest way to send your files around the world

we.tl

 

[Legacy 143009]

typo on my behalf. massive thanks !!!!!!!!!!!!!!!!!!!!!!!!!!!!! 1 year been trying to fix that

Ahh, gald to hear it did work! Thanks for the feedback

 

[FGaxha]

Try, this will help:
Insert Module and copy paste the code. Select sell exm. Sheet1 B2 . Also in each Tab write the formula to send back to Main Tab "Summary"

Sub CreateLinksToAllSheets()
Dim sh As Worksheet
Dim cell As Range
For Each sh In ActiveWorkbook.Worksheets
If ActiveSheet.Name <> sh.Name Then
ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"'" & sh.Name & "'" & "!A1", TextToDisplay:=sh.Name
ActiveCell.Offset(1, 0).Select
End If
Next sh
End Sub

 

[leecavturbo]

Ahh, gald to hear it did work! Thanks for the feedback

only issue now is the links are broken if the file name is changed . ??

 

[Legacy 143009]

Please find the example:

WeTransfer - Send Large Files & Share Photos Online - Up to 2GB Free

WeTransfer is the simplest way to send your files around the world

we.tl

 

[leecavturbo]

Please find the example:

WeTransfer - Send Large Files & Share Photos Online - Up to 2GB Free

WeTransfer is the simplest way to send your files around the world

we.tl

Ah man, link expired , my bad , sorry dude, could you again

 

[Legacy 143009]

This should work even if you change your file name:

=HYPERLINK(MID(CELL("filename",A1),FIND("[",CELL("filename",A1)),FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))+1)&"Sheet1!$A"&SHEET(),"My Link")

Note: "filename" stays as it is. Do not replace it with your file name.

 

[leecavturbo]

I'm back! can the above #18 link back to column i row 3 as the first reference row ?

 

[Legacy 143009]

Like this?

=HYPERLINK(MID(CELL("filename",A1),FIND("[",CELL("filename",A1)),FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))+1)&"Sheet1!$A"&2+SHEET(),"My Link")

You have to adjust number 2 according to your sheet name. So if your sheets start from 1, then 2+1 will be 3. If your sheets start from 2 you must write 1+SHEET()