Dependent drop down lists with dates

Benten559 · Feb 21, 2019

Hi all!
Really new to excel projects and upon doing some research on my own i find myself stuck..
I am currently working on making a time card to be submitted via excel.

Currently the user will select the appropriate pay period from a drop down menu in a cell. Let’s say A1. The pay period they select is of the format such as: 2/16/19-3/1/19. The drop down menu is populated from a list “payperiods2019” I have created as a row in another sheet.
What I need help with:
Looking to create a drop down list which will provide the employee the given dates within the pay period range so they can start filling out a time card in another column.

What I’ve tried:
I’ve looked into making dependent lists using INDIRECT() but my issue has been the naming convention on the lists. Since I can’t title them by the pay period a user selects I haven’t been able to associate the list of dates I have created in data validation.

Any help is appreciated,
Thanks!

GFelts · Feb 21, 2019

Select the contents in the ROW with the dates are and use it in the validation drop down. If the dates have been entered as TEXT use VALUE to convert them to numbers. make sure the cell is formatted as Dates.

G

Benten559 · Feb 21, 2019

Thanks!
This works well no doubt!
I had considered it originally but being that there are so many pay periods between now and the end of the year, it seemed to tedious to name every range of possible dates and include them within an of statement.
Any idea how I could make that work faster for me?
Thanks again!

GFelts · Feb 21, 2019

Not sure why you would need to name anything. Use the Drop down to select the start date. If your using the 15th as a payday and the End of the Month as a pay day use EOMONTH() to calculate the last date. ie..If you enter 1/1/19 into cell A1 then put =EOMONTH(A1,0) into A2 it will put 1/31/2019 in Cell A2. There you can massage it however you want. A2-A1 will produce an answer of 30 days (You will have to add 1 day to get the actual number of days for the month of Jan as you are not counting 1/1/19 as that is the day you are start counting from). Then have your Employee fill out the rest of the form. You can automate some of the information with formulas so if they work 8 hours a day and cell B3 has the answer to A2-A1, which is 30 then you have 30 days x 8 hours or an answer of 240. A ton of ways you can massage these numbers. Don't forget you can use IFERROR to prevent the dreaded errors filling the spots where formulas are at but no data to calculate with.

Welcome to the board by the way. I'm relatively new myself.

G

Benten559 · Feb 21, 2019

Good looking out on the IFERROR! Also thanks for the welcome, ive already learned a couple new tricks! However still working on this one here.
After a bit of research I found out how to include some of my worksheet here.
Take a look at what im currently working with if you can and see if you can give some guidance on how to complete what im trying to accomplish.
I was hoping to A2 would have the billing week for the employee to choose from, A8 would include all the values inclusive dates within a drop down menu.

Thanks again for any direction

[B]Excel 2016 (Windows) 32 bit[/B] [SIZE=1][TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH][/TH]
[TH][COLOR=#FFFFFF]A[/COLOR]
[/TH]
[TH][COLOR=#FFFFFF]B[/COLOR]
[/TH]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "][CENTER][COLOR=#FFFFFF][B]2[/B][/COLOR][/CENTER]
[/TD]
[TD][CENTER]7/22/19-8/4/19[/CENTER]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "][CENTER][COLOR=#FFFFFF][B]3[/B][/COLOR][/CENTER]
[/TD]
[TD][CENTER]Week[/CENTER]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/SIZE][TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: [B]Timecard[/B][/TD]
[/TR]
</tbody>[/TABLE]

[B]Excel 2016 (Windows) 32 bit[/B] [SIZE=1][TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH][/TH]
[TH][COLOR=#FFFFFF]A[/COLOR]
[/TH]
[TH][COLOR=#FFFFFF]B[/COLOR]
[/TH]
[TH][COLOR=#FFFFFF]C[/COLOR]
[/TH]
[TH][COLOR=#FFFFFF]D[/COLOR]
[/TH]
[TH][COLOR=#FFFFFF]E[/COLOR]
[/TH]
[TH][COLOR=#FFFFFF]F[/COLOR]
[/TH]
[TH][COLOR=#FFFFFF]G[/COLOR]
[/TH]
[TH][COLOR=#FFFFFF]H[/COLOR]
[/TH]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "][CENTER][COLOR=#FFFFFF][B]7[/B][/COLOR][/CENTER]
[/TD]
[TD][CENTER][I]Date[/I][/CENTER]
[/TD]
[TD][CENTER][I]Time In[/I][/CENTER]
[/TD]
[TD][CENTER][I]Time Out[/I][/CENTER]
[/TD]
[TD][CENTER][I]Night Pay[/I][/CENTER]
[/TD]
[TD][CENTER][I]Code[/I][/CENTER]
[/TD]
[TD][CENTER][I]Activity[/I][/CENTER]
[/TD]
[TD][CENTER][I]Loc[/I][/CENTER]
[/TD]
[TD][CENTER][I]Duration[/I][/CENTER]
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "][CENTER][COLOR=#FFFFFF][B]8[/B][/COLOR][/CENTER]
[/TD]
[TD][/TD]
[TD][RIGHT]12:00:00 AM[/RIGHT]
[/TD]
[TD][RIGHT]1:00:00 PM[/RIGHT]
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Fitness/Sports[/TD]
[TD]Belcher[/TD]
[TD][RIGHT]=MOD([COLOR=#0000FF]RC[-5]-RC[-6],1[/COLOR])[/RIGHT]
[/TD]
[/TR]
</tbody>[/TABLE]
[/SIZE][TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: T[B]imecard[/B][/TD]
[/TR]
</tbody>[/TABLE]

In Admin Lists worksheet:
[B]Excel 2016 (Windows) 32 bit[/B] [SIZE=1][TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH][/TH]
[TH][COLOR=#FFFFFF]A[/COLOR]
[/TH]
[TH][COLOR=#FFFFFF]B[/COLOR]
[/TH]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "][CENTER][COLOR=#FFFFFF][B]1[/B][/COLOR][/CENTER]
[/TD]
[TD]Pay Period:[/TD]
[TD][CENTER]2/18/19-3/3/19[/CENTER]
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "][CENTER][COLOR=#FFFFFF][B]2[/B][/COLOR][/CENTER]
[/TD]
[TD]Inclusive Dates:[/TD]
[TD][CENTER]2/18/2019[/CENTER]
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "][CENTER][COLOR=#FFFFFF][B]3[/B][/COLOR][/CENTER]
[/TD]
[TD][/TD]
[TD][CENTER]2/19/2019[/CENTER]
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "][CENTER][COLOR=#FFFFFF][B]4[/B][/COLOR][/CENTER]
[/TD]
[TD][/TD]
[TD][CENTER]2/20/2019[/CENTER]
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "][CENTER][COLOR=#FFFFFF][B]5[/B][/COLOR][/CENTER]
[/TD]
[TD][/TD]
[TD][CENTER]2/21/2019[/CENTER]
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "][CENTER][COLOR=#FFFFFF][B]6[/B][/COLOR][/CENTER]
[/TD]
[TD][/TD]
[TD][CENTER]2/22/2019[/CENTER]
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "][CENTER][COLOR=#FFFFFF][B]7[/B][/COLOR][/CENTER]
[/TD]
[TD][/TD]
[TD][CENTER]2/23/2019[/CENTER]
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "][CENTER][COLOR=#FFFFFF][B]8[/B][/COLOR][/CENTER]
[/TD]
[TD][/TD]
[TD][CENTER]2/24/2019[/CENTER]
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "][CENTER][COLOR=#FFFFFF][B]9[/B][/COLOR][/CENTER]
[/TD]
[TD][/TD]
[TD][CENTER]2/25/2019[/CENTER]
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "][CENTER][COLOR=#FFFFFF][B]10[/B][/COLOR][/CENTER]
[/TD]
[TD][/TD]
[TD][CENTER]2/26/2019[/CENTER]
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "][CENTER][COLOR=#FFFFFF][B]11[/B][/COLOR][/CENTER]
[/TD]
[TD][/TD]
[TD][CENTER]2/27/2019[/CENTER]
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "][CENTER][COLOR=#FFFFFF][B]12[/B][/COLOR][/CENTER]
[/TD]
[TD][/TD]
[TD][CENTER]2/28/2019[/CENTER]
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "][CENTER][COLOR=#FFFFFF][B]13[/B][/COLOR][/CENTER]
[/TD]
[TD][/TD]
[TD][CENTER]3/1/2019[/CENTER]
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "][CENTER][COLOR=#FFFFFF][B]14[/B][/COLOR][/CENTER]
[/TD]
[TD][/TD]
[TD][CENTER]3/2/2019[/CENTER]
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "][CENTER][COLOR=#FFFFFF][B]15[/B][/COLOR][/CENTER]
[/TD]
[TD][/TD]
[TD][CENTER]3/3/2019[/CENTER]
[/TD]
[/TR]
</tbody>[/TABLE]
[/SIZE][TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: [B]Admin Lists[/B][/TD]
[/TR]
</tbody>[/TABLE]

Akuini · Feb 21, 2019

Here’s another way:
Create the name range, say “AA” & “BB” etc for the Inclusive Dates, and put the names below Pay Period (see example below):
And use a formula like this for the data validation:

Code:

=INDIRECT(HLOOKUP(A2,Sheet2!$B$2:$C$3,2,FALSE))

Excel 2013 32 bit
[Table="width:, class:head"][tr=bgcolor:#008B8B][th] [/th][th]

A

[/th][th]

B

[/th][th]

C

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

2

[/td][td]Pay Period:[/td][td]

1/1/19-10/1/19

[/td][td]

11/1/19-20/1/19