Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lists with dates in them
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lists with dates in them
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 ================================================== ====================== TheNewbie wrote: 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.a...excel/200801/1 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lists with dates in them
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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "TheNewbie" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to clear validation lists based on other validation lists | Excel Discussion (Misc queries) | |||
LISTS- adding info without repeat to other lists | Excel Discussion (Misc queries) | |||
Lists referencing lists...is it possible? | Excel Worksheet Functions | |||
Multiple lists with repeated values for dependet drop down lists | Excel Worksheet Functions | |||
lists from other lists in excel | Excel Worksheet Functions |