hyperlink to be more intelligent

[xtasio]

Hello all,

I am working in an spreesheet in which in the first tab there is an index and in the second tab it is all the information. The index in the first tab has hyperlinks so when you click in a particular point you are directed to the relevant area in the second tab.

The problem is that each time I introduce a new row in the second tab the information is displaced one row down but the hyperlink stays fix and therefore lossing the relation beteween the point in the index and the information in the tab.


Is there a way around to make the hyperlink to stick the cell (with) the content initiatially linked to so when you introduce a column/row the end of the hyperlinks moves with the information initially linked it to?

 

[Ron Coderre]

Here's a way to use the HYPERLINK function to refer to the second tab...
This formula initially refers to cell C10 on Sheet2:

=HYPERLINK(CELL("address",Sheet2!$C$10),"First Range"))

If you insert rows or columns that move that cell...that formula will refer to the new location.

Is that something you can work with?

 

[xtasio]

Hello Ron,

Thanks for your prompt reply.

The formula definatelly works in new open excells, I ve'nt managed to make it work in excells already with information.

What do you think it could be the reason for this?

Best Regards

 

[xtasio]

Following your lead I have found this formula that it seems to work in every case

=HYPERLINK("#"&CELL("address",'INDICATORS TR'!B3),"Click me")

Do you knoe what the "#"& means?

Many thanks

 

[Ron Coderre]

Following your lead I have found this formula that it seems to work in every case

=HYPERLINK("#"&CELL("address",'INDICATORS TR'!B3),"Click me")

Do you knoe whatthe "#"& means?

Many thanks

The "#" causes Excel to treat what follows as an link within the workbook.