Insert a link with a dynamic Cell Reference

[fapb48]

Hi,

Is there a way to add a dynamic link on excel?

lets say i had a link on B1.. and clicking on B1 it points me to a cell on a table in another sheet within the same worksheet, lets say B10..

But when that table gets sorted and the content on that B10 cell gets moved to another row i want that link to still point at the actual content and not to B10.

Hopefully it makes sense.

Thanks

 

[ChrisFoster]

Assuming that the table is in Sheet2 and the column you are interested in is Column B, and you want the hyperlink to point to the value in Column A of the same row, you can use the following formula:

=HYPERLINK("#Sheet2!"&ADDRESS(MATCH(B1,Sheet2!B:B,0),1),INDEX(Sheet2!A:A,MATCH(B1,Sheet2!B:B,0)))

This formula uses the MATCH function to find the row number of the cell containing the value in B1 within Column B of Sheet2. The ADDRESS function then uses this row number to create a reference to the cell in Column A of the same row. Finally, the INDEX function returns the value in that cell.

When you click on the hyperlink in cell B1, it will take you to the correct cell in Sheet2, regardless of its position within the table.

 

[fapb48]

Assuming that the table is in Sheet2 and the column you are interested in is Column B, and you want the hyperlink to point to the value in Column A of the same row, you can use the following formula:

=HYPERLINK("#Sheet2!"&ADDRESS(MATCH(B1,Sheet2!B:B,0),1),INDEX(Sheet2!A:A,MATCH(B1,Sheet2!B:B,0)))

This formula uses the MATCH function to find the row number of the cell containing the value in B1 within Column B of Sheet2. The ADDRESS function then uses this row number to create a reference to the cell in Column A of the same row. Finally, the INDEX function returns the value in that cell.

When you click on the hyperlink in cell B1, it will take you to the correct cell in Sheet2, regardless of its position within the table.

This is great, thank you.

Altough, i forgot to mention a little (big) detail.

I want to be able to insert this type of link on an image/icon and then when i click on the image it points me to that dynamic cell.

Really sorry i wasnt very explicit first time...

 

[fapb48]

Anyone that could help with this, please?