Lists with dates in them

[TheNewbie]

I am trying to come up with a way to have a dropdown list that displays all
the days of the month, and when you choose one of the dates, it takes you to
the corresponding cell on the page (employees are listed vertically, and days
of the month are listed horizontally accross the page, with a section under
each date for each employee). Here is an example of what I am talking about:

Emp1 Date1 Date2 Date3 Date4...
Emp2 Date1 Date2 Date3 Date4...
Emp3 Date1 Date2 Date3 Date4...

So when I click on date4 in the dropdown list, it moves me over to the date4
cell, just for an example.

I know how to create a macro that would do this, but I don't want to have to
have 31 buttons to correspond to each day of the month, I just want one list
that has all the macros in it. I don't even know if this is possible.

 

[smw226 via OfficeKB.com]

Hi,

Normally if I am working with dates I would imbed the calendar control.

Todo this:
First make sure that the control toolbar is visible.
Click on "More Controls" at the very end
Choose "Calendar Control" which will add it to your worksheet.

If you double click on the control (in design mode) it will take you into the
VBA editor where you can choose the "After Update" action to trigger your
macro.

You can acces the selected date via:
Me.Calendar1.Value

HTH

Simon

========================================================================

I am trying to come up with a way to have a dropdown list that displays all
the days of the month, and when you choose one of the dates, it takes you to
the corresponding cell on the page (employees are listed vertically, and days
of the month are listed horizontally accross the page, with a section under
each date for each employee). Here is an example of what I am talking about:

Emp1 Date1 Date2 Date3 Date4...
Emp2 Date1 Date2 Date3 Date4...
Emp3 Date1 Date2 Date3 Date4...

So when I click on date4 in the dropdown list, it moves me over to the date4
cell, just for an example.

I know how to create a macro that would do this, but I don't want to have to
have 31 buttons to correspond to each day of the month, I just want one list
that has all the macros in it. I don't even know if this is possible.

--
--------------------
Simon - UK

Email at simon22mports [ a t ] hot mail [ d ot ]com

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200801/1

 

[Max]

Here's one play using hyperlinks to deliver the underlying intents ..

Illustrated in this sample:
http://www.freefilehosting.net/download/3b46i
Hyperlink matching name n date in another sht.xls

Source data below assumed in sheet: z, in col A across

Emp1 Date1 Date2 Date3 Date4...
Emp2 Date1 Date2 Date3 Date4...
Emp3 Date1 Date2 Date3 Date4...

In another sheet: x,
Data validation lists to select the Names & Dates are assumed in B2:C2 down

In D2
=IF(COUNTA(B2:C2)<2,"",HYPERLINK("#"&CELL("address",INDIRECT("'z'!"&ADDRESS(MATCH(B2,z!A:A,0),MATCH(C2,OFFSET(z!$1:$1,MATCH(B2,z!A:A,0)-1,),0),1,1))),B2&"-"&TEXT(C2,"dd-mmm-yy")))
Copy D2 down as far as required. Col D will return the hyperlinks to jump to
the matched name/date in z. Adapt to suit.

---