Hyperlink scroll trough sheet based on cell value

[MihailR]

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]...[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[TD]Z[/TD]
[TD]AA[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]Dates[/TD]
[TD]8/30/16[/TD]
[TD]...[/TD]
[TD]4/13/17[/TD]
[TD]4/14/17[/TD]
[TD]4/15/17[/TD]
[TD]4/16/17[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Description[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Foundation Concrete Pour[/TD]
[TD]4/13/17[/TD]
[TD]4/16/17[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]













I have a table with items and the time each takes to be completed (Columns A, B, C) and a timeline which shows the distribution of units for each day in columns D and beyond.
Panes are frozen at cell D3. I would like to create a hyperlink (or any other method) which would allow me to click on cell C4 ( or any other cell on row 4) and based on C4 value to scroll on the sheet and show me the timeline.
I know I can do this manually by creating a hyperlink for cell C4 and referencing cell AA2. But this is ineffective and time consuming, since there are some spreadsheets with 40000 rows.

Thank You for your help, its greatly appreciated.

 

[Jeffrey Mahoney]

and based on C4 value to scroll on the sheet and show me the timeline.

What would the value in C4 be? Would you scroll to a different place each time? Is it always column AA?

 

[MihailR]

C4 is a different date in each row. The dates in column D... and beyond do not change place.
(Row 2 contains dates for each day in the next 2 years, there are no changes to the data column D and beyond)
Based on the value of column C(4,5,6,7..) the hyperlink to correspond to a date in Row 2.
(possible issue with placing the reference on row 2 is if I am looking at value in cell C597 (9/15/18),
and it is tied to 9/15/18 in row 2 i will completely lose my placement (view) of row 597.
So I guess the reference link would have to be in column same as value 9/15/18 in row 2, and row
same as the cell whose value is being searched (597 in this case).

Hope this explains the situation better.
Again thank you for your time.

 

[Jeffrey Mahoney]

Ok, here's what I think you want. You want to scroll right to the correct date associated with the date in column C for each row.

How about a macro that gets triggered when you double-click the date in column C?

I need you to create a named range. Select all of your Dates across the top and create a named range called "DatesRng". Paste this code in the Sheet level module of the VBA project. Alt-F11. Double click on the Sheet name you are working under Microsoft Excel Objects in the Top left Pane. Paste this code in the Top Right Pane.

Now go double click on a date in column C

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim i As Range
  Dim D As Date
  Dim Cel As Range
  
  Set i = Intersect(Target, Range("C4:C20000"))
  If Not i Is Nothing Then
    Cancel = True
    D = Int(i.Value)
    For Each Cel In Range("DatesRng")
      If Int(Cel.Value) = D Then
        Intersect(i.EntireRow, Cel.EntireColumn).Select
      End If
    Next Cel
  End If
  
End Sub

 

[MihailR]

Worked like a charm. Thank You very much.