Log In
Register
Home
Trusted Members
February 13, 2021
Offline
I am creating a calendar for employee scheduling, I would like it to add total hours scheduled and alert the supervisor if you are scheduling more than x number of hours. I can get a conditional format to work but I really would like a written data validation but I am having issues (my assumption is because it is on a separate worksheet). Is there a way for data validation to cross worksheets? When all is said and done I will have one sheet per department and many employees cross departments. I am using excel 2013 and have the file attached.
Trusted Members
December 20, 2019
Offline
Your Issue is because you are merging cells, the attached has data validation working on the top box.
There are 3 main rules when using excel,
1) dont merge cells
2) dont merge cells
3) dont merge cells
Seriously they are the worst thing you can do on any worksheet, if you want the look of merged cells then selects the cells and use centre across selection instead.
Trusted Members
February 13, 2021
Offline
That didn't work for me? Maybe I should try a message box...
July 16, 2010
Offline
Hi Jessica,
Your question isn't quite clear. Where is the 'x number of hours' limit stored in the spreadsheet?
To back Purfleet up, I agree, Merge & Center is evil. Please use Center Across Selection instead.
The formula in cell C6 of the Employees sheet should be a SUMIF:
=SUMIF('WRCC Control Desk'!$C$12:$C$90,Employees!A6,'WRCC Control Desk'!$BB$12:$BB$90)
Please clarify what you're wanting to do i.e. where the 'x number of hours' is stored, where you want the 'written data validation' to appear and what it should say.
Thanks,
Mynda
Trusted Members
February 13, 2021
Offline
Good Morning!
I have been making the improvements Purfleet suggested. Honestly I'm not sure why I didn't use the simplified Sumif function, thank you for that! The total hours scheduled are stored in column C of the "Employees" worksheet, it should say something along the lines of "Your employee is currently scheduled x number of hours for the week. Would you like to continue?" then I have conditional formatting to highlight the employee. And now that I say it out loud, I'm going to have to rethink the formula because as of now it's calculating for the month.
1 Guest(s)
