Hi all,
Is it possible to achieve the following scenario with the VLOOKUP formula?
Basically this is a schedule where I want to populate the cells from E7 to E54 based on the selection of E5
The cells should be populated with whats in E3
Book1.xlsx
Thank you
What should be in the cells if shift1 is selected at E5?
What should be in the cells if shift2 is selected at E5?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
It should display the agent code (what is on row line 5)
Now something I miss to explain/add:
The formula to add from E7 to E54 will be dependent on the E5
For example: if agent SDC (E3) is scheduled for Shift 1 (E5) the E column will be populated with the agent code from 0:30 until 9:30
Rest will be with nothing
If Shift 2 is selected then it will populate from 04:00 till 11:00
Last edited by exceleron; 07-21-2015 at 04:38 AM.
No - still not enough information, but it might be getting closer. Please confirm. Shift 1 = 00:30 to 09:30. Yes or No.
Shift 2 = ???
Other shifts??
I could add them later if I get the formula principle, either way they will be:
* Only applicable to Site 1 (please see row 1 legend)
Shift 1 = 00:00 to 09:00
Shift 2 = 13:00 to 10:00
* Only applicable to Site 2
Shift 3 = 05:00 to 14:00
Shift 4 = 07:00 to 16:00
* Only applicable to Site 3
Shift 5 = 08:00 to 16:30
Shift 6 = 10:30 to 19:00
Shift 7 = 13:45 to 22:45
* Only applicable to Site 4
Shift 8 = 12:00 to 21:00
Shift 9 = 14:30 to 23:30
Shift 10 = 16:00 to 01:00
is this too much?
Probably not... BUT. I've been moving furniture from house-to-house today and now I'm wrecked. It's also beer-o'clock so anything I do now might be more error-prone than normal. I'll be back in the morning and will take a look at this then....
Thanks Glenn
Cheerio!
Try in E7 and copy down
=IF(AND($E$5="Shift 1",D7>=TIME(0,0,0),D7<=TIME(9,0,0)),$E$3,IF(AND($E$5="Shift 2",D7>=TIME(13,0,0),D7<=TIME(22,0,0)),$E$3,""))
Awesome! It works John
Now, for some reason If I want to adjust time of the schedule it automatically adjust to PM
=IF(AND($E$5="Shift 1",D7>=TIME(0,0,0),D7<=TIME(9,0,0)),$E$3,IF(AND($E$5="Shift 2",D7>=TIME(13,0,0),D7<=TIME(22,0,0)),$E$3,""))
What is in red I tried to adjust to start at 01 AM and finish at 10 AM but when I press enter it converts to PM
How to avoid automatic conversion?
Originally Posted by JohnTopley
=IF(AND($E$5="Shift 1",D7>=TIME(0,0,0),D7<=TIME(9,0,0)),$E$3,IF(AND($E$5="Shift 2",D7>=TIME(1,0,0),D7<=TIME(10,0,0)),$E$3,""))
See attached
Awesome, thanks a bunch
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks