Lookup Table?

[handysmurf]

OK I don't think this is a hard one but here goes ... I don't think I can do this without explaining. I have a spreadsheet for keeping track of parenting info for a divorced couple (I'm a paralegal). I need to plug in who's day it is supposed to be for every day. Right now I just set it up manually and just copy the rotation. I wanted to automate this because then it is less likely mistakes will be made. I thought about nested formula's but I can't nest that many. I'm thinking a lookup table might work but I'm not good enough with excel to figure it out. Method doesn't matter I just want it to work.

For those familiar it is a 2-2-3 schedule or see the rotation in the image above. This just repeats every two weeks. I will just reference cells as they appear above and change to fit my actual references later. So I was doing simple If formulas but that still requires me to copy and paste them in two week blocks to the next two weeks. I want a formula or something so that it is just a formula in column D that can just be copied to all cells in Column D. It needs to key on what day it is in Column B and whether or not Column C = "Parenting" because there are other rows in between. I'm thinking a lookup table might be the answer but I just can't wrap my head around working that out atm.

TIA

 

[Vogateer]

You need some sort of reference to distinguish between week 1 and week 2, and a lookup table is an easy way to do it. There might be another formula that could determine it, but it might be easier for you to read the formula for the lookup table like I posted above. Did you try that formula and find that it didn't work?