Drop Down and Auto Insert

[Guest]

I have a payroll form where I would like to have a drop down for PayPeriod of
1-26 for all weeks in the year.

After selecting the current pay period between 1 and 26 I would like Excel
to automatically insert the Beginning Report Date in one cell and the through
date in another cell.

For instance if 01 is selected the two cells would be 12/25/05 and 01/07/06.

I then have all the days for the two weeks in rows going down the form with
the day of the week and the date next to it.

I have the spreadsheet working now such that I can enter the pay period I
want to enter manual and it fills the cells all the way through. I am trying
to add the feature to be able to select the pay period from a driop down list
rather than manually enter.

What kind of macro or thing would I need to do to create the drop down
feature? I have created Names for Reportdate and Through which have the
corresponding 26 dates each would have as a start since I think this would be
needed if a macro is used.

 

[Guest]

This can be done without any code. On a seperate sheet in the workbook add
the numbers 1-26 in column A. In B and C add the appropriate end dates for
tose periods. My preference would be to use named ranges for something like
this. Name the Range A1:A26 PayPeriodList and the range A1:C26 PayPeriodDates.

Now on the sheet you intend to use in the cell where you want the drop down
add a validation list (Data->Validation->List =PayPeriodList). In the cells
where you want the dates add a lookup formula =Vlookup(A2, PayPeriodDates, 2,
false)... where A2 is the pay period number that you got from the drop down
list.

 

[Rowan Drummond]

You don't need a macro to have dropdown functionality in a cell. This
can be done with data validation. See excel help and Debra Dalgleish's
notes at:
http://www.contextures.com/xlDataVal01.html

Hope this helps
Rowan

 

[Norman Jones]

Hi Bdehning,

Try:

'=============>>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim RngStartDate As Range
Dim RngEndDate As Range
Dim i As Long

Set rng = Range("A1") '<<==== CHANGE
Set RngStartDate = Me.Range("C1") '<<==== CHANGE
Set RngEndDate = Me.Range("D1") '<<==== CHANGE

If Target.Count > 1 Then Exit Sub

If Not Intersect(Target, rng) Is Nothing Then
RngStartDate.Value = #12/25/2025# + 14 * (rng.Value - 1)
RngEndDate = RngStartDate.Value + 14
End If
End Sub
'<<=============

This is worksheet event code and should be pasted into the worksheets's code
module (not a standard module and not the workbook's ThisWorkbook module):

*******************************************
Right-click the worksheet's tab

Select 'View Code' from the menu and paste the code.

Alt-F11 to return to Excel.
*******************************************