Quickly jump to last row in a data set using excel hyperlink function
Today I´ll show you how to create a useful hyperlink in excel. If you click the link it will take you to the first empty row in a data set.
Formula in cell C2:
Explaining the formula in cell C2
Step 1 - Find the last nonempty cell in cell range B1:B100
MATCH(CHAR(255),B1:B100,1)
becomes
MATCH(ÿ,B1:B100,1)
and returns 8. If your data set is bigger than row 100, expand cell range B1:B100 further down.
Step 2 - Join file name, sheet and cell address
"[Quickly jump to last row using hyperlinks.xlsx]Sheet1!$B$"&MATCH(CHAR(255),B1:B100,1)+1
becomes
"[Quickly jump to last row using hyperlinks.xlsx]Sheet1!$B$"&8+1
becomes
[Quickly jump to last row using hyperlinks.xlsx]Sheet1!$B$9
Step 3 - Create the hyperlink
HYPERLINK("[Quickly jump to last row using hyperlinks.xlsx]Sheet1!$B$"&MATCH(CHAR(255),B1:B100,1)+1,"Hyperlink")
becomes
=HYPERLINK([Quickly jump to last row using hyperlinks.xlsx]Sheet1!$B$9,"Hyperlink")
You can also use this keyboard shorcut to find the next empty cell: Ctrl + Arrow
Download excel *.xlsx file
Quickly jump to last row using hyperlinks.xlsx
Functions in this post
MATCH(lookup_value, lookup_array, [match_type])
Returns the relative position of an item in an array that matches a specified value in a specific order
How to quickly find the maximum or minimum value [Formula]
Question: I have three columns and how do I identify the largest and smallest number? Where is the value? I […]
How to use the HYPERLINK function
The HYPERLINK function allows you to build a link in a cell pointing to something else like a file, workbook, cell, […]
Create links to all sheets in a workbook
The macro demonstrated above creates hyperlinks to all worksheets in the current worksheet. You will then be able to quickly […]
Use hyperlinks in a pivot table
Sean asks: Basically, when I do a refresh of the data in the "pivotdata" worksheet, I need it to recognise […]
Locate lookup values in an Excel table [HYPERLINK]
Today I'll show you a formula that returns a hyperlink pointing to a location based on a lookup value. When […]
List all hyperlinks in worksheet
The macro in this blog post lists all hyperlinks in a worksheet. It also lists all words beginning with "http" […]
9 Responses to “Quickly jump to last row in a data set using excel hyperlink function”
Leave a Reply to Martin
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 < and > becomes >
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.
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))
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
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?
=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.
Randy,
MATCH(410000,Sheet2!E3:E1002,1)
Is your list sorted in an ascending order?
Read this:
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")
Leonid,
great comment.
Thank you!
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
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/