Nested Ifs Logical Help to auto populate a schedule

Hey All,

Ok so I know what I want to do but I'm having issues coming up with the logic for the formulas.

Ok so I have dates and on those dates there is a type of shift being a single, double, triple, or quad shift. These shifts are scheduled out a month at a time and are every six days. So what I have the sheet doing is finding the six largest date values and finding what kind of shift it is. I use 6 because that is the most shifts you can have in 1 month. I will add in a function to only pull the date if it is the same month later. Ok so once it has pulled the type of shift what I want it to do is then In a column put the date as many times as the type of shift it is. So if it is a single it would put the date once then it would go to the next shift or a triple would put it down three times and then go to the next one. From there I will pull the data from the assignment sheet to find the shift and fill in the persons name.

My main issue is that the logic running down the date column for all the possibilities I'm have trouple figuring it out. There is a good chance there is an easier way to do it than a ton of nested ifs and if so that would be awesome. I put a column next to it showing desired outcome. If possible it would be even better if it could go in ascending order rather than descending but I wasn't sure if that would be possible.

Any help anyone can give me would be awesome. Thanks.

Jeremy

Logic Help.xlsx

Hi

This is based on your example file.

D9: =E1
D10:

Please Login or Register  to view this content.

This formula has to be array entered (ctrl, shift, enter)
Copy down from D10 to D20.

HTH

rylo

rylo,

Hey sorry I haven't been able to thank you. Your formula for my sample data worked perfectly and I was hoping to be able to use it for my goal but I haven't had any luck. So now I'm uploading the whole file with a tab that shows what I would like to have happen.

Basically I want to have a tab for each month and on that tab have a template where it would pull the data from the Schedule sheet and auto-populate a monthly schedule that I can print out. The catches are the single double triple and quad thing like before, the ability that if on the schedule sheet it dec 2012 is still there when they start dec 2013 that it will pull the most recent year's month, and that the Chow and Super shifts are for each shift that day not just one like the others.

Thanks for any help you or anyone else can give me.

Jeremy
Forum Help.xlsx

Hi

Can you update your example file and show what the required output should look like, and how it relates to the source data.

rylo

Rylo,

I did in my second post the file below my name called "Forum Help.xlsx" has my full data with all pages and formulas. On the "dec" tab it shows the template and then below it it shows the desired output taken from the "Schedule" page.

Thanks for looking at my problem I really appreciate your help.

Jeremy

HI

Here goes

This is all done on sheet Dec
1) Copy the values from N3:N6 and put them in P3:P6
2) B18: =K18
3) B19: =IF(COUNTIF($B$18:B18,B18)=MATCH(OFFSET($J$19,0,SUM(1/COUNTIF($B$18:B18,$B$18:B18))),{"Single","Double","Triple","Quad"},0),OFFSET($J$18,0,SUM(1/COUNTIF($B$18:B18,$B$18:B18))+1),B18) Array entered, and then copy down to B23
4) D18: =INDEX(Schedule!$A:$A,MATCH(VLOOKUP($C18,$O$3:$P$6,2,FALSE)&" "&D$3,OFFSET(Schedule!$A:$A,,MATCH($A18,Schedule!$1:$1,0)-1),0)). Copy down / across to G23
5) H18: =INDEX(Schedule!$A:$A,MATCH(H$3,OFFSET(Schedule!$A:$A,,MATCH($A18,Schedule!$1:$1,0)-1),0)) Copy down / across to I23.

This will match the example output you have in Dec. The issue of Chow and Super still remain. Given what I can see in this file, I think you will have to add the shift prefixes to be able to distinguish them.

HTH

rylo

Rylo,

Thanks for you quick reply. I think I was a bit unclear on my example. The set of dates with the type of shift in cells K18:O19 I had just pulled over to know what dates had what shifts when I was setting up the example data. Ultimately this will be used for 6 different section for them to each put in their set of dates and shifts.

What I would like to have this do is have a tab for each month
Have a formula that searches for any dates in that month from the "Schedule" tab Row 1 and populate the corresponding dates and shifts into the B4:I## for each month's tab based on what type of shift it is.

So in the example I had put I meant that those december dates would be found on the "Schedule" tab And I was planning on putting the data from K3:O12 on the "Formulas" tab so all that would actually be on the Monthly tabs would be the schedule and I could simply print that tab pre formatted. If this is too much I understand. The auto populating schedules aren't a necessity. The people I'm doing this for are already happy with the functionality it has. I just figured why put in all that and then make them have to copy everything to a new tab to print out the schedule.

Let me know if that is clearer and can actually be done. I could probably figure out a way to do a lookup to pull the corresponding shifts and who worked them my main problem is having it search from Schedule!J1:CH1 for the most recent Jan, Feb, Mar etc and give me those dates back in order by number of shifts

Thanks again and sorry for bugging you.

Jeremy

One last time. Can anyone help with this? Thanks.