How to enter a date that then hyperlinks to the sheet with corresponding date

[Haunted Ape]

Hello,

I have received some great advice on here before and I'm hoping someone can help me again.

I'm looking to enter a date in cell G2 on sheet 1 that then looks for a matching date within cell F2 on all the other sheets within the same workbook (each day of the month has it's own sheet) and then hyperlinks to that corresponding sheet.

Can anyone advise how this can be done please?

Many thanks for your time.

 

[Oeldere]

why not all data on 1 worksheet (e.g. with a macro).

It makes it a lot easier to anlyze the data (e.g. with a pivottable).

 

[Perpa]

Hello,

I have received some great advice on here before and I'm hoping someone can help me again.

I'm looking to enter a date in cell G2 on sheet 1 that then looks for a matching date within cell F2 on all the other sheets within the same workbook (each day of the month has it's own sheet) and then hyperlinks to that corresponding sheet.

Can anyone advise how this can be done please?

Many thanks for your time.

Haunted Ape,
Here is some VBA that will do the job...A Worksheet Change_Event.
It builds a hyperlinked Index Sheet to all worksheets in the workbook.
First add a new sheet to the Workbook and call it "myIndex". Whatever sheet you want to enter the date in cell G2 must have this code. Make sure you have dates in cell F2 for all the other worksheets.
I chose to enter the date on Sheet 'myIndex'…So right click on the sheet name tab of the "myIndex" sheet and select "View Code".
Select the arrow to the right of the 'General' ribbon and select 'Worksheet'.
Copy and paste the following code making sure you don't have two 'Sub' and 'End Sub' lineswhich are added when you select 'Worksheet'.
Close and save your workbook as 'macro enabled'. You should try this on a copy of your workbook so you don't lose anything.
There is a link placed in cell A1 on each worksheet linking 'Back to myIndex' sheet. You can change that to any cell you like.
Perpa

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$G$2" Then
    Dim wSheet As Worksheet
    Dim l As Long
    l = 1
    
        With Sheets("myIndex")
            .Columns(1).ClearContents
            .Cells(1, 1) = "INDEX"
            .Cells(1, 1).Name = "Index"
        End With
        For Each wSheet In Worksheets
            If wSheet.Name <> "myIndex" Then
                l = l + 1
                    With wSheet
                        .Range("A1").Name = "Start_" & wSheet.Index
                        .Hyperlinks.Add Anchor:=.Range("A1"), Address:="", SubAddress:="Index", TextToDisplay:="Back to myIndex"
                    End With
                    
                    With Sheets("myIndex")
                      .Hyperlinks.Add Anchor:=.Range("A" & l), Address:="", SubAddress:=wSheet.Name & "!F2", TextToDisplay:=wSheet.Name & "  " & wSheet.Cells(2, "F")
                    End With
                    
            End If
        Next wSheet

        Dim s As String
        Set x = Sheets("myIndex").Columns(1).Find(Cells(2, "G"))
        s = Left(x, InStr(1, x, " ", 1) - 1)
        Sheets(s).Activate
    End If
    End Sub