9 Responses to “Quickly jump to last row in a data set using excel hyperlink function”

1. David Hager says:

   August 4, 2013 at 6:45 pm

   Oscar, I played around with this type of MATCH formula long ago and found it to be a little buggy. Noenetheless, I have found that the most general form of this formula is:

   =MAX(MATCH(0,A:A,-1),MATCH(CHAR(255),A:A,1))

   Reply
   • Oscar says:

     August 5, 2013 at 10:51 am

     David Hager,

     You are right, I remember sam´s comment now:
     https://www.get-digital-help.com/2011/04/28/create-a-dynamic-named-range-in-excel/#comment-24493

2. Randy says:

   December 9, 2013 at 7:41 pm

   I am at a complete loss with this. This hyperlink function should work perfectly but it completely ignores the array that I program it to look within and returns me to a cell that is outside of the lookup array.

   =HYPERLINK("[CompletePull.xlsx]'Sheet2'!$E$"&MATCH(410000,Sheet2!E3:E1002,1)+1,1)

   I am attempting to match the entered date and return the cursor to the empty cell below on sheet two. My cursor returns to cell C1 instead. Would anyone tell me where I am going wrong here? My lookup array is clearly Sheet2 E3:E1002. Why would it even return my cursor to anything outside of that range?

   Reply
3. Randy says:

   December 9, 2013 at 7:56 pm

   =HYPERLINK("[CompletePull.xlsx]'Sheet2'!$E$"&MATCH(410000,Sheet2!E3:E1002,1)+3,1)

   Okay, I have figured out how to get this syntax to work for dates. For some reason I had to tell the match formula to return the reference plus 3. I am having a hard time dissecting that but if anyone can help me with it, it would be much appreciated.

   Thank you Oscar, I've been trying to figure this one out for a long time.

   Reply
   • Oscar says:

     December 11, 2013 at 3:17 pm

     Randy,

     MATCH(410000,Sheet2!E3:E1002,1)

     Is your list sorted in an ascending order?

     Read this:

4. Leonid says:

   May 1, 2018 at 1:36 am

   Oscar, let's use CELL("filename") to make a filename-sheetname part dynamic, so, combined with David's suggestion, it will look like: HYPERLINK(REPLACE(CELL("filename"),1,FIND("[",CELL("filename")),"[")&"!B"&MAX(MATCH(0,B:B,-1),MATCH(CHAR(255),B:B,1))+1,"HYPERLINK")

   Reply
   • Oscar says:

     May 1, 2018 at 6:45 am

     Leonid,

     great comment.

     Thank you!

5. Martin says:

   March 6, 2019 at 6:56 am

   What is wrong with the below formula? When I click on the hyperlink, the cursor stops at A2270. Tried out various options, yet cursor stops at A2270.

   =HYPERLINK("[BANK STATEMENT.xlsx]ICICI$c$"&MATCH(CHAR(255),C2:C3000,1)+1,"BACK")

   Help !

   Thanks,

   Martin

   Reply
   • Oscar says:

     March 6, 2019 at 10:26 am

     Martin,

     Perhaps there is a CHAR(255) character in C2270?

     This article demonstrates a formula that extracts ANSI characters:
     https://www.get-digital-help.com/2018/01/26/identify-all-characters-in-a-cell-value/

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes &lt; and > becomes &gt;

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form