Hyperlink to today's date

[Grim/Don]

I've got a scheduling spreadsheet with 10+ lines for each day. Is ther
a way to set up a hyperlink in A1 so that it jumps to "today" withou
me having to edit the link each day?

Thanks in advance

 

[David McRitchie]

You could use a doubleclick event macro. To find the date if they are in order
http://www.mvps.org/dmcritchie/excel/event.htm#match

Just a guess, because you don't tell us what the format is, do those
10+ lines each begin with a date in Column A.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

 

[Grim/Don]

Just looked at the sheet again. The day of the week (ex: Monday...) is
in a 3-row-merged cell (in column A), the numeric date (ex: 9/17/04) is
right below in another 3-row-merged cell (in column A). So, 6 lines per
day. (This is sometimes expanded if we have more orders per day...)

But, yeah, the date is in column A. I've got 2,650+ rows to the sheet
and if I go off looking for older (or future) orders, i'ts a lot of
scrolling to get back to 'today'

I have cel A2 available and put in a hyperlink to A1838. Today, I
edited the link to A1844. (Also have conditional formatting to turn the
cell with today's date green so it stands out).

I tried:

=hyperlink(today(),today), (and variations thereof), but always got an
error.

Thanks for the help.

 

[David McRitchie]

Hi ....,

If the cell with Monday is a date formatted as dddd
then the following Event macros will bring you to that cell
there is also scrolling factor so you can see the date immediately above.

Looking for the date, scolls to 6 rows above, activates cell 3 rows above date

Automatic when workbook is selected, and you can doubleclick
anytime to get to the day of the week above the current date.
Tested in Excel 2000.

Right click on the sheetname, view code, .insert the following

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim val As Long, r As Long
val = Date
Cancel = True
r = Application.Match(val, Range("A2:A65536"), 1) + 1
Application.Goto Cells(Application.Max(2, r - 9), 1), Scroll:=True
Application.Goto Cells(r - 3, 1)
End Sub

Private Sub Worksheet_Activate()
Dim val As Long, r As Long
val = Date
r = Application.Match(val, Range("A2:A65536"), 1) + 1
Application.Goto Cells(Application.Max(2, r - 9), 1), Scroll:=True
Application.Goto Cells(r - 3, 1)
End Sub

Read more on Event macros at
http://www.mvps.org/dmcritchie/excel/event.htm


If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value.
you can change to 0 if dates are not in order for an exact match