Hello Excellers and welcome back to another #FormulaFriday #Excel blog post in my 2019 series. Do you ever need to jump to today’s date in your Excel workbook?. Do you manually scroll along?. In this blog post today, I want to share a formula that I have used in a recent spreadsheet solution. The user needs to be able to jump to today’s date by clicking on a hyperlink. The hyperlink is labeled simply ‘JUMP TO TODAY’. This would take the user to the column that relates to today’s date. So, here is a sample of the data structure and solution is below.
So, here is a simple way to click on the hyperlink and to take you the cell that relates to today. How do we do that?. We actually can use a combination of three functions. CELL, INDEX, and MATCH. The full formula can be seen below. We will then work through the logic step by step.
Step 1. Create A Named Range.
The first thing I want to do is created a named range for my range of dates. We will use this in the first stage of the formula. There are a few ways which we can create a named range. Well, three that I have used. If you want to read in more detail then please read my dedicated blog post below.
Three Reasons To Use Named Ranges.
So, I have created my named range by selecting the range of dates, selecting the Name Box and entering the name fo your range. I have named the range DATES. Hit enter to finish.
Step 2. Use INDEX and MATCH.
The next step uses the INDEX and MATCH functions to find a match of ‘Todays’ date in my named range DATES.
=INDEX(DATES,MATCH(TODAY(),DATES,0))
The MATCH function gets the position of the value of TODAY() inside the named range data. Therefore, the lookup value in the MATCH function is TODAY().
This result then goes into the INDEX function as row_num, with “DATES” as the array. We get a correct match which is 18/09/2019 – this is the date I am writing this blog post.
Step 3. Extract CELL Address.
Next, the MATCH position is passed to the CELL function. This extracts the CELL address of today’s date.
The CELL function is a useful one. It returns information about a given cell. One of the options is the Address. So this is exactly what we needed for this solution.
The Syntax Of The CELL Function.
=CELL(info_type, [reference])
The CELL function syntax contains the following arguments:
info_type This is a required argument. A text value that specifies what type of cell information you want to return.
reference This is the cell that the information is to be returned for.
Note: If a range of cells is supplied, the returned information relates to the top-left cell of the range; If the reference is omitted, then the returned information relates to the last cell that was changed.
Step 4. Hyperlink To Today’s Date.
So, the final step in this solution is to hyperlink to the cell address that is today’s date. The Syntax of HYPERLINK is
= HYPERLINK(link_location, [friendly_name])
where
link_location is the path and file name to the document to be opened. It can refer to a place in a document — such as a specific cell or named range in an Excel worksheet or workbook, or to a bookmark in a Microsoft Word document.
friendly_name This argument is optional. The jump text or numeric value is displayed in the cell. Friendly_name is displayed in blue and is underlined. If friendly_name is omitted, the cell displays the link_location as the jump text.
In our solution, the link_location is generated by concatenation of “#” and the cell address generated by the rest of our formula. The second argument It is given the reference “Today”.
The Final Formula.
So, the final function is ready. Time to test it. Best bit right?. Yes for me too.
If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
Likewise, if you want to see all of the blog posts in the Formula Friday Series Click The Link Below.
How To Excel At Excel – Formula Friday Blog Posts.
So, Don’t forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month. Just click on the link and enter your email. No spam. Just Excel tips.
Finally, I am delighted to let you know I have teamed up with Excel Rescue. Have you got a problem with Excel?. Get Help with Excel – Fast! Excel Rescue is a done-for-you small tasks service for Microsoft Excel. Why don’t you check out how they can help you. Click on the link below for special offers on Excel solutions. Book mark the link for furture reference.