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 ?

 

[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.

 

[Legacy 143009]

=HYPERLINK("[Book1.xlsx]Sheet1!$A$"&5+SHEET(),"My Link")

This formula will add 1 to row referance after 5th row each time a new sheet is copied.

 

[leecavturbo]

=HYPERLINK("[Book1.xlsx]Sheet1!$A$"&5+SHEET(),"My Link")

This formula will add 1 to row referance after 5th row each time a new sheet is copied.

Cannot open the specified file. ? yes i changed the book1 to the file name

 

[Legacy 143009]

Maybe you must include the filepath:

=HYPERLINK("C:\User\Desktop\[Book1.xlsx]Sheet1!$A$"&5+SHEET(),"My Link")

I haven't tested it like this. You must fiddle around.

 

[leecavturbo]

Maybe you must include the filepath:

=HYPERLINK("C:\User\Desktop\[Book1.xlsx]Sheet1!$A$"&5+SHEET(),"My Link")

I haven't tested it like this. You must fiddle around.

seemingly the simplist of hyperlink doesnt work, even if i insert function hyper link and actually click on the location . the kyperlink is created but doesnt work ! ? eg =HYPERLINK(Sat!I3,"back") Sat being the worksheet in the open book

 

[Legacy 143009]

Ok, find my sample file. Copy Sheet 2 and see how it functions:

Book1.xlsx

1 file sent via WeTransfer, the simplest way to send your files around the world

we.tl

 

[leecavturbo]

@Flashbond Ah that works but i notice my books are .xlsm. ( that the difference ? ) i even renamed my book to book5 for simplification and recreated you simple hyperlink ( which is great and perfect in your book ) but i get a 'Cannot open specified file.'

 

[Legacy 143009]

Have you tried:
=HYPERLINK("[Book1.xlsm]Sat1!$A$"&SHEET(),"My Link")

 

[leecavturbo]

Have you tried:
=HYPERLINK("[Book1.xlsm]Sat1!$A$"&SHEET(),"My Link")

yes