Hi all,

Background: I'm improving the functionality of a Promotions Tracker for work.

I'm working on a shared file in which the users have to enter a row number in cell B1 and cell A1 then takes that data and concatenates it into a hyperlink to a different sheet. She sheet varies depending on another cell but this is probably not relevant here. The overall goal is to have a 'key'/hyperlink to link the user to the first row of each promotion's data (which will be on a range of rows on another sheet).

For example:

User inputs '10' into B1.
A1 (using this formula below) generates a link to the tenth row on Sheet 2:

=HYPERLINK("#"&CELL("address",INDIRECT("Sheet2!$A$"&B1)),"A "&B1)

I need to know how to maintain the correct row number if the user deletes or adds rows. For example, if the user deletes rows 1 to 9, I want the hyperlink to reference the new row in which the data appears (1, in this case), not row 10 again. I guess I'm trying to maintain a reference to the data, not the row.

Maybe I'm thinking about it wrongly. I'm not sure. But I can't think of any logical way to make this work.

Notes: I'm aware that one can use 'names' for the cells, but this requires too much effort for the users. I also would prefer not to use VB.

Please help, and thank you!