Drop Down List

[dtmd]

I have 2 separate needs:

1. I would like to create a drop down list for choices of various dates
(beginning of pay periods). I can't figure out how to do this.

2. I would like to set something up so that if a specific date is chosen -
one of my fields on the timesheet will autopopulate with 8 hours for the
holiday that falls within that pay period.

If you chose 1-9-2005 - the 2 weeks of dates within that payperiod will be
calculated. I want the drop down to be I3 and when the date of 1/8/2005 is
chosen - 1/17/2005 (B22) - would have the Holiday cell (T22) autopopulated
with an 8. An automatic fill of that cell would be nice as well.

 

[Debra Dalgleish]

You can create a dropdown list with data validation. There are
instructions in Excel's Help, and here:

http://www.contextures.com/xlDataVal01.html

You'll create a list of dates for the dropdown list, and in the column
to the right, you could enter the number of holiday hours for that
period. In cell T22, use a VLookup formula, to return the holiday hours
for the selected date, e.g.: =VLOOKUP(B22,DateList,2,0)

There's information on VLOOKUP here:

http://www.contextures.com/xlFunctions02.html

 

[dtmd]

Okay - Thank you for the guidance - I was able to get my pay period begin
date drop down list to work just fine.

However - I'm still VERY confused on the part of when a payperiod begin date
is chosen of having the 8 hours auto populate the exact column (Holiday -
column T) and row (Date of holiday - rows 11-15 and 20-26). I created the
look up table with the date of the holiday and the number of hours for that
day. I still don't get how when the user chooses the payperiod begin date -
let's say - 8 Jan 2005 - how I get the 8 hours to autopopulate in the
appropriate date for column T and the row that has populated with the holiday
date - which for this example is 17 jan 2006 which will fall in row 22 this
time. (T22) If they chose another PP begin date - say 19 feb 2006 - then
the holiday is 20 feb 2006 which would now be T11.

It's so difficult to explain this... wish you could see what I'm trying to do.

 

[dtmd]

Oh - and the date's that are auto populated for the payperiod upon selection
are in Column B10-16 and B21-27.