Hyperlink or jump to another row in same column?

Question

Is there a way to 'jump to' a certain row in the same column without having to manually set up each hyperlink or formula (meaning I can copy/paste), and set it to change accordingly whenever I insert or delete a column? I want to be able to link to row 40 of the column they are in, regardless of what letter the column is.

I would like to place a link or formula in cell B3 that says "see row 40 below for more information" and clicking that link will take you down to cell B40. The link in C3 takes you to C40; D3 goes to D40, E3 to E40, etc. This is so I can put all of the important info at the top of the sheet, and then they can jump to row 40 to see some other less-important info that is related to row 3.

I want the formula or hyperlink to change accordingly whenever I add or delete columns, so that I don't have to manually edit the hyperlink or formula every time. Currently (for example) if I set cell C3 to link to C40 then I insert a new column between column C and column D, the data shifts but my hyperlink does not. The new column that I inserted between C and D becomes the new column D and my old data is now in column E, but my hyperlink still points to column D unless I manually adjust it. I'd like it to automatically adjust the link whenever the column letter changes.

Answer 1

Something like:

=HYPERLINK("#" & SUBSTITUTE(ADDRESS(1,COLUMN(),4,1),"1","") & "30","down")

will always be a jump link to a cell in row #30 of the column in which the formula resides.

(If you insert/delete columns, the formula will adjust.)

This is because:

=SUBSTITUTE(ADDRESS(1,COLUMN(),4,1),"1","")

yields a column letter(s):