Dynamic hyperlink that behaves as linked cell + linked cell

[UnicornHunter]

Hi,

This is my first post. I am normally a bit of a lurker. I tried for about 4 hours to work this out yesterday and few hours today on this one.

I am using Excel 2010 and Windows 7 64bit.

I am using an excel workbook that is updated and changed constantly, Rows are added daily, columns occasionally and formatting changes are made quite often also.

The linked cells works perfectly (copy>right click>paste options>Paste Link). When I add rows, change sheet names or make formatting changes the cell stays linked to the correct cell.

However the hyperlink uses the cell address which goes into error if the sheet name changes or I add rows above that cell. So basically if the hyperlink behaved exactly as the linked cells (by default) I would not have a problem. I have seen a number of threads which recommend using the hyperlink function in a formula, unfortunately I have limited experience with excel code. Using the hyperlink function in a formula also seems as if it will introduce a second problem. I then need to have two separate formulas in the cell; this would be a difficult situation for me.

The closest I got to having he problem solved was this post:
http://www.mrexcel.com/forum/excel-questions/557532-dynamic-hyperlink-cell-value.html
or so I thought anyway.
So is this called a dynamic hyperlink? in that case is a link(in the case of linked cells) automatically dynamic?

I pretty good with computers, and tech in general and have always been able to work through problems without help, but I have never found MS office to be the most intuitive suite of software for me personally, but I digress.

The end result I that am aiming for is a cell that is linked in such a way to the original that if the text (which is also the easy name in the hyperlink) in the original is changed it is changed in the linked cell + the hyperlink always goes to the original, regardless of what is done around it, i.e rows added etc

Thanks guys.

 

[Andrew Poulsom]

Welcome to MrExcel.

For a dynamic hyperlink to another worksheet you can use a formula like:

=HYPERLINK(CELL("address",Sheet2!A1),Sheet2!A1)

 

[UnicornHunter]

Wow that was quick. Thanks so much for the reply. I gave it a go and it is nearly working! the exciting part is that the dynamic part is working! I tried adding rows and the cell reference changed which is awesome.
I ended up with =HYPERLINK(CELL("address",'Brisbane_&_Surrounding'!C468),'Brisbane_&_Surrounding'!C468)

But when I click it to action the hyperlink I get a message saying "Reference is not valid", which is weird because the easy_name is correct.

 

[Andrew Poulsom]

It won't work if the sheet or workbook name contain spaces or other characters that result in the reference being surrounded by single quotes. That's because the CELL function returns eg:

'[Book1.xlsm]Brisbane_&_Surrounding'!$C$468

but the HYPERLINK function expects:

[Book1.xlsm]'Brisbane_&_Surrounding'!$C$468