If functions and Drop down lists

MZING81

New member
Joined
Mar 27, 2012
Messages
40
Reaction score
0
Points
0
Hello everyone,

Thanks for all the help that I have received since being a member. I have a 5 data validation lists on one sheet. They each have specific criteria referencing another sheet, but it only a small portion functions. I have an if formula for an hourly staffing report, that references each individual day in the week, but only works on Sunday.


=IFERROR(IF('SCHEDULE WEEK ONE'!$J5="","",IF(AND(ISNUMBER(SEARCH($P$15,'SCHEDULE WEEK ONE'!$L5)),SEARCH($P$19,'SCHEDULE WEEK ONE'!$C5),SEARCH($P$23,'SCHEDULE WEEK ONE'!$E5),SEARCH($P$27,'SCHEDULE WEEK ONE'!$F5),SEARCH($P$31,'SCHEDULE WEEK ONE'!$G5)),(RIGHT('SCHEDULE WEEK ONE'!$J5,1))+0,"")),"")


I want it to pull a start shift time and end shift time to match the criteria. I have two columns that are one for start time, and end time and another thats combined. the one shown is combined. This one doesnt not pull the end shift time! Hopefully I explained this properly. Also should I use something other than data validation list?
 

MZING81

New member
Joined
Mar 27, 2012
Messages
40
Reaction score
0
Points
0
Hello everyone,

Thanks for all the help that I have received since being a member. I have a 5 data validation lists on one sheet. They each have specific criteria referencing another sheet, but it only a small portion functions. I have an if formula for an hourly staffing report, that references each individual day in the week, but only works on Sunday.


=IFERROR(IF('SCHEDULE WEEK ONE'!$J5="","",IF(AND(ISNUMBER(SEARCH($P$15,'SCHEDULE WEEK ONE'!$L5)),SEARCH($P$19,'SCHEDULE WEEK ONE'!$C5),SEARCH($P$23,'SCHEDULE WEEK ONE'!$E5),SEARCH($P$27,'SCHEDULE WEEK ONE'!$F5),SEARCH($P$31,'SCHEDULE WEEK ONE'!$G5)),(RIGHT('SCHEDULE WEEK ONE'!$J5,1))+0,"")),"")


I want it to pull a start shift time and end shift time to match the criteria. I have two columns that are one for start time, and end time and another thats combined. the one shown is combined. This one doesnt not pull the end shift time! Hopefully I explained this properly. Also should I use something other than data validation list?


Here's a sample, it should have start and times based on criteria selected from the data validation lists. Also I would like to add a selection for ALL criteria's, and to calculate the number of individuals on the right side beside and below the times.
View attachment SAMPLE FOR HOURLY STAFFING.xlsx
 

MZING81

New member
Joined
Mar 27, 2012
Messages
40
Reaction score
0
Points
0
Here's a sample, it should have start and times based on criteria selected from the data validation lists. Also I would like to add a selection for ALL criteria's, and to calculate the number of individuals on the right side beside and below the times.
View attachment 777


I solved the main issue with the function. Except for the fact that the data extraced with the data validiation drop down is showing times that are a minute and some seconds off. I have no idea why.

MZING81
 
Top