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.
--
Brian

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.
--
HTH...

Jim Thomlinson


"bdehning" wrote:

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.
--
Brian

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

bdehning wrote:
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.