Turn last non-blank cell address formula into a Hyperlink

[2Took]

Formula returns address of last non-blank cell in column A: ="A"&MATCH(2,1/(A:A<>""),1)
e.g.: A1000

Trying to turn it into a Hyperlink with: =HYPERLINK("A"&MATCH(2,1/(A:A<>""),1))
results in below error, when clicking on it.

How to turn last non-blank cell address formula into a Hyperlink that works?

 

[jasonb75]

You need to prefix the reference with the # symbol in order for it to work.

3 Versions of your formula, the first 2 being more efficient.

Numeric data in column A (including dates, times, etc).

=HYPERLINK("#A"&MATCH(1e+100,A:A)))

Text data in column A

=HYPERLINK("#A"&MATCH(REPT("z",255),A:A))

Mix of text and numeric data in column A

=HYPERLINK("#A"&MATCH(2,1/(A:A<>""),1))

 

[2Took]

=HYPERLINK("#A"&MATCH(E1+100,A:A))
returns
#N/A
I need it to be a hyperlink, showing address of last non-blank cell, that can be clicked on and it should take you to that cell.

 

[2Took]

Third one worked. Thanks!

=HYPERLINK("#A"&MATCH(2,1/(A:A<>""),1))

 

[jasonb75]

=HYPERLINK("#A"&MATCH(E1+100,A:A))
returns
#N/A
I need it to be a hyperlink, showing address of last non-blank cell, that can be clicked on and it should take you to that cell.

Probably didn't work because you've typed the formula incorrectly. If you look at my post, it says 1e+, not E1+

 

[2Took]

=HYPERLINK("#A"&MATCH(1E+100,A:A))
same #N/A

 

[jasonb75]

What about the second formula?

If you have formula blanks in column A then that one might not pick up the correct row, but this one will.

=HYPERLINK("#A"&XMATCH("?",A:A,2,-1))

Please bear in mind that I can not see your screen and I am not psychic. What I know about your data is what you tell me, nothing more.