Hyperlink scroll trough sheet based on cell value

MihailR

New Member
Joined
Mar 27, 2017
Messages
3
ABCD...XYZAA
1
2Dates8/30/16...4/13/174/14/174/15/174/16/17
3DescriptionStart DateEnd Date
4Foundation Concrete Pour4/13/174/16/1720202020
5

<tbody>
</tbody>













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.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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?
 
Upvote 0
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.
 
Upvote 0
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

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top