Creating hyperlinks in Excel that are relative & actually work

If you've ever tried to create a hyperlinks in excel you'll know what a pain they are.

The hyperlink tool on the Insert ribbon is quite useful, but it is a pain to create a lot of them. The big problem however is that they are not relative - if you add more columns or rows suddenly your hyperlink is pointing to the wrong place.

You could use the custom hyperlink formula, this will let you create a lot of entries more quickly, however it is still not relative and has more issues.

=HYPERLINK("[Budget]June!E56", E56)

This is because the address is entered as text so is not a cell address. Also we now have the problem that the spreadsheet name is now also hardcoded so if you change the spreadsheet name you've now scuppered youself.

Pretty crappy really seeing as this is Micro$ofts own tutorial! So in the next evolution of its usuage would be to add the filename in dynamically using the following function:

=CELL("filename",C9)

However to be able to use it in the formula we need to extract just the filename so we have to use:

=LEFT(CELL("filename",C10),FIND("]",CELL("filename",C10)))

This is also very clumsy as we are still harcoded - not relative - and trying to dig ourselves out of the predicament we are going to make it even more complicated.

The solution

What we need is a subtle tweak, this is the answer:

=HYPERLINK("#"&CELL("address",M235),"Actuals Summary")

Nice, simple and hitting all the buttons.