Hyperlinks

ps50150

New Member
Joined
Jan 22, 2018
Messages
6
Hi

I have a set of data with dates across the columns for each day going for a whole year for example

In the first columns I want to put today's date for example 18-Mar-19, then i want to hyperlink that so it goes to the correct column in Excel which could be for example column ZG, cell reference doesn't matter too much can be "ZG3"as an example.

I can't get the hyperlink to be dynamic, ideally I want to use the Today() formula and the excel hyperlink moves 1 column as each day passes....

Let me know if an example helps!

Cheers
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,794
Office Version
  1. 2013
Platform
  1. Windows
If your saying you have all the dates for this year for example in Row(1) starting in Column(B)

And you want to enter a date in Range("A1") and then you want the active cell to be the cell with the date you entered in Range("A1")

Here is a sheet event script that will do what you want.

I know of no way how this can be done with just a simple Hyperlink

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Now when you enter a date in Range("A1") the script will run

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  3/19/2019  12:57:24 AM  EDT
If Target.Address = Range("A1").Address Then
Dim LastColumn As Long
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
Dim SearchString As String
Dim SearchRange As Range
SearchString = Target.Value
Set SearchRange = Cells(1, 2).Resize(, LastColumn).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox SearchString & "  Not Found": Exit Sub
SearchRange.Select
End If
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,108,509
Messages
5,523,313
Members
409,511
Latest member
hitesh222002

This Week's Hot Topics

Top