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

 

[dreid1011]

Maybe I'm missing something here, but if it's a rotating two week schedule, why can't you copy and paste two weeks worth of columns B:D and then drag down the date in column A to fill in, rinse and repeat? Or 4 weeks, 6 weeks, etc?

Anyway, using a lookup table implies there is a unique ID in the first table you can use to match with a unique ID in the lookup table, then return some results. To me, your data setup does not look like it needs a lookup table. Can you post more data with expected results? Maybe some unfinished rows and what data you need to fill in and how you determine what data is filled in and where.

 

[Burrgogi]

Maybe I'm missing something here, but if it's a rotating two week schedule, why can't you copy and paste two weeks worth of columns B:D and then drag down the date in column A to fill in, rinse and repeat? Or 4 weeks, 6 weeks, etc?

Anyway, using a lookup table implies there is a unique ID in the first table you can use to match with a unique ID in the lookup table, then return some results. To me, your data setup does not look like it needs a lookup table. Can you post more data with expected results? Maybe some unfinished rows and what data you need to fill in and how you determine what data is filled in and where.

The OP is rather unclear in her posting but here is my guess at it:

First two days of the wk (Mon, Tue) = Parent #1
Next two days of the wk (Wed, Thur) = Parent #2
Remaining three days of the wk (Fr,Sat,Sun) = Parent #1

Then this cycle repeats the following week:

First two days of the wk (Mon, Tue) = Parent #2
Next two days of the wk (Wed, Thur) = Parent #1
Remaining three days of the wk (Fr,Sat,Sun) = Parent #2

 

[dreid1011]

The OP is rather unclear in her posting but here is my guess at it:

First two days of the wk (Mon, Tue) = Parent #1
Next two days of the wk (Wed, Thur) = Parent #2
Remaining three days of the wk (Fr,Sat,Sun) = Parent #1

Then this cycle repeats the following week; but I'm fairly certain Parent #2 will kick off the cycle rather than parent #1.

That was my impression to, which to me does not beg the use of a lookup table. I would just copy two weeks worth of a filled in schedule (columns B:D), then paste and drag down column A to auto fill the date...

A lookup table in this case would just be duplicating the data in the original table with no unique data to "look up".

 

[Vogateer]

Does the schedule stay consistent, such that you could calculate the number of days from the previous schedule and know that the pattern has repeated every time since then?

I don't know how your table is set up, but if you're determined to use a lookup table with a formula, you could start with the table you have above as the lookup table, adding a series column to serve as an index.

I created the table F1:H15, and named it "ParentIndex". You could also name the K1 cell if you like, but the value of K1 has to match the first date in the ParentIndex Table (G2 in my image) for this formula to work. The index also has to go from 0 to 13.

For Column D, starting with D2, I used this formula:

=LET(index,MOD(A2-$K$1,14),INDEX(ParentIndex[Parent], MATCH(index, ParentIndex[Index], 0)))

This is a pretty simple formula and idea. You take the number of days between the date in column A and $K$1, divide by 14, and the remainder of that operation should give you the Index number. Use that to match the first column in the ParentIndex table, and you should be good.

 

[dreid1011]

Disregard my previous posts, I overlooked the part about there being other rows in between the "Parenting" rows. Please post a sample of your data including the other rows.

 

[Vogateer]

Does the schedule stay consistent, such that you could calculate the number of days from the previous schedule and know that the pattern has repeated every time since then?

I don't know how your table is set up, but if you're determined to use a lookup table with a formula, you could start with the table you have above as the lookup table, adding a series column to serve as an index.

I created the table F1:H15, and named it "ParentIndex". You could also name the K1 cell if you like, but the value of K1 has to match the first date in the ParentIndex Table (G2 in my image) for this formula to work. The index also has to go from 0 to 13.

For Column D, starting with D2, I used this formula:

=LET(index,MOD(A2-$K$1,14),INDEX(ParentIndex[Parent], MATCH(index, ParentIndex[Index], 0)))

This is a pretty simple formula and idea. You take the number of days between the date in column A and $K$1, divide by 14, and the remainder of that operation should give you the Index number. Use that to match the first column in the ParentIndex table, and you should be good.

Sorry, I forgot to include a test to make sure the column had "Parenting" in the column:

=IF(C2="Parenting", LET(index,MOD(A2-$K$1,14),INDEX(ParentIndex[Parent], MATCH(index, ParentIndex[Index], 0))), "")

 

[handysmurf]

Maybe I'm missing something here, but if it's a rotating two week schedule, why can't you copy and paste two weeks worth of columns B:D and then drag down the date in column A to fill in, rinse and repeat? Or 4 weeks, 6 weeks, etc?

Anyway, using a lookup table implies there is a unique ID in the first table you can use to match with a unique ID in the lookup table, then return some results. To me, your data setup does not look like it needs a lookup table. Can you post more data with expected results? Maybe some unfinished rows and what data you need to fill in and how you determine what data is filled in and where.

dates are filled with a series, days key on date returning a day format. there are rows in between and columns in between I just hid them to show it here. I'm already just doing the cut and paste thing by two week blocks

 

[handysmurf]

The OP is rather unclear in her posting but here is my guess at it:

First two days of the wk (Mon, Tue) = Parent #1
Next two days of the wk (Wed, Thur) = Parent #2
Remaining three days of the wk (Fr,Sat,Sun) = Parent #1

Then this cycle repeats the following week:

First two days of the wk (Mon, Tue) = Parent #2
Next two days of the wk (Wed, Thur) = Parent #1
Remaining three days of the wk (Fr,Sat,Sun) = Parent #2

Yes that is exactly the cycle

 

[handysmurf]

See image. Not married to a lookup table ... just not good enough at excel so that was my only other guess as to how to make it work