This is kind of embarrassing, but I thought I had this worked out with lots of assistance in another excel forum. Since then, some things changed and the now it’s not working and I almost have to start from scratch. Here’s the problem: I have students in 3 separate groups and each group has different ‘follow-up’ requirements based on their ‘assignment month’ and everything has to be tied to the current date such that it’s easy to identify what each students next follow-up month is (based on their group). The whole thing looks like it should be doable w/ some formula, but that’s well beyond my skill set. I’ve attached the sheet with a couple of expected results and welcome any questions. Thanks.

Attached Files

SAMPLE_PROBLEM.xlsx (13.6 KB, 15 views)

The first thing that needs to be done with this worksheet is some slightly different formatting. What you are after can be accomplished with some vlookup and month formulas. The issue is your data is not separated into cells. The question is does your worksheet need to stay formatted like this? If it does we can work around it but it will be a lot more formulas to pull it off.

From what you've described, I believe you can use:
=TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())+MOD(DATEDI F(TODAY(),DATE(YEAR(TODAY())+1,MONTH(DATEVALUE("1-"&$G23&"-"&YEAR(TODAY())))+1,0),"ym"),(12/2^($D23-1))),1),"MMM")
in I23 (assuming that's where the expected results are to go), then copied down as far as needed.

FWIW, with the above formula, you don't really need the group tables (they aren't referenced). I'll leave it to you to figure out how the formula calculates the review intervals for each group. However, if you want to output the review schedule for each student, you would need to keep the group tables and use:
=OFFSET($A$1,MATCH($G23,$B$5:$B$16,0)+3,MATCH("Gro up " &$D23,$B$2:$O$2,0)+2)
in, say, J23, then copied down as far as needed.

PS: The board software puts spaces in the first formula where there shouldn't be any. In this case, 'DATEFI F' is 'DATEDIF'.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Actually no. It can look like anything, but it seemed to be the best way to explain the problem. My preference would be that the tables are elsewhere (if they're needed at all). Best case, a calculation in a cell and no look up tables. Thanks.

Thanks. This may solve many a problem (for me anyway). I'll start playing with it this morning and feed back once I'm sure I haven't screwed something up.

Do you need to keep the layout, inculding the merged cells ( always causing problems) ?

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

We have a winner!

It works like a champ. I'm poking at it a bit to understand what you did, but thanks a bunch. It will save me lots of time and very elegantly I must say.

Sorry I missed this earlier. I was playing with the solution macropod provided. And to answer your question, I'm not married to any of the formatting, particularly merged cells. I'm just kind of a visual person and I thought it helped explain it. Thanks.

Well I thought we had it solved, but I guess I was too eager. Someone found a flaw and I rechecked the original. For some reason it’s not working properly. The expected result for line 21 (Student 19) should be May, but it shows up as Apr. I went back and checked the original sample and the formula

=TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())+MOD(DATEDI F(TODAY(),DATE(YEAR(TODAY())+1,MONTH(DATEVALUE("1-"&$G23&"-"&YEAR(TODAY())))+1,0),"ym"),(12/2^($D23-1))),1),"MMM")

gave the same results. Everything was fine until the date changed to 04/29/14 then the result changed from May (which was correct) to April which is wrong. It should have been May until 06/01/14 then it would change to Aug. Thanks for looking at it again and I do appreciate your time.

Attached Files

Revised Sample Problem.xlsx (14.9 KB, 20 views)

In F3, try:
=TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())+MOD(DATEDI F(DATE(YEAR(TODAY()),MONTH(TODAY()),1),DATE(YEAR(T ODAY())+1,MONTH(DATEVALUE("1-"&$E3&"-"&YEAR(TODAY())))+1,0),"ym"),(12/2^($D3-1))),1),"MMM")
then copy down as far as needed.

PS: As previously advised, the board software puts spaces in the formula where there shouldn't be any. In this case, 'DATEFI F' is 'DATEDIF' and 'T ODAY()' is 'TODAY()'

Note: If you change the cell format, to 'mmm', you could reduce the formula to:
=DATE(YEAR(TODAY()),MONTH(TODAY())+MOD(DATEDIF(DAT E(YEAR(TODAY()),MONTH(TODAY()),1),DATE(YEAR(TODAY( ))+1,MONTH(DATEVALUE("1-"&$E3&"-"&YEAR(TODAY())))+1,0),"ym"),(12/2^($D3-1))),1)

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

That may just do it. I tried to figure it out on my own based on your original solution, but I couldn't reverse engineer it so I had to come back. It seems to be OK, but because I haven't had a chance to see what you changed yet, I haven't figured out why it works. Then again, I may never. I'll play with it tomorrow at work and make sure I'm not premature in calling it fixed, but I can't tell you how much I appreciate your continued effort. Chat tomorrow.