Hyperlinks

[ps50150]

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

 

[My Aswer Is This]

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

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