The portion of my "beasty" formula that you see below works well.

=IF(AND(TODAY()EDATE(AL7,12),AA7<"",AL7<"",AN7= "Referred to Tx"),"Updated
Eval Needed Before New RF",IF(OR(AA7<"",AND(TODAY(
AG7,AN7="AC Initiated")),IF(AND(TODAY()AG7,AN7="AC Initiated"),IF(AP7="","Check AC Status",IF(AND(TODAY()EDATE(AP7,6),AN7="AC Initiated"),"Re-Check AC Status","")),IF(AND(TODAY()EDATE(AA7,12),AN7="Ref erred to TX"),IF(AL7="","Eval Before RF",

The string below (which attaches to my "beasty" formula above) has been
separated out from the above formula for the purpose of clarity in this
posting.

It seems to work appropriately (creating the Confirm AC Follow Through Flag)
ONLY when a date is input into cell AA7. However, the string of formula below
should ALSO "register" and produce the applicable flag (Confirm AC Follow
Through), when a date input into cell AL7 (and NO date is placed in cell AA7).

IF(AND(TODAY()EDATE(AL7,6),AN7="Referred to Tx"),"Confirm AC Follow
Through","")),"")),

This last string of the formula (which comes after that which you see
above) does work, and flags according to the logic.

IF(AL7<"",IF(AND(TODAY()EDATE(AL7,12),AN7="Refer red to Tx"),"New Eval
Needed",""),"")))

So now that I almost have it, any final suggestions to tame this beast? I
suspect that I need to add in one more piece of logic in order for the
formula to work 100%. Again, it is only the string I pointed out (under the
single condition mentioned) that doesn't seem to want to play :(

Thanks very much for any help offered!
HAPPY 4th of July all!

Dan

I think part of the problem is that you can only have 7 levels of embedded if
statements.

one method to get around this is to use choose() if you have 29 or fewer
options
=choose((AND(TODAY()EDATE(AL7,12))+2*AND(TODAY()
AG7,AN7="AC Initiated")+3*...,"Updated Eval Needed Before New RF","Check AC Status", ...)
You will have to take care that your options are not duplicated and are
appropriate for the choose sselection such that onely one will be correct at
any given time.


"Dan the Man" wrote:

The portion of my "beasty" formula that you see below works well.

=IF(AND(TODAY()EDATE(AL7,12),AA7<"",AL7<"",AN7= "Referred to Tx"),"Updated
Eval Needed Before New RF",IF(OR(AA7<"",AND(TODAY(
AG7,AN7="AC Initiated")),IF(AND(TODAY()AG7,AN7="AC Initiated"),IF(AP7="","Check AC Status",IF(AND(TODAY()EDATE(AP7,6),AN7="AC Initiated"),"Re-Check AC Status","")),IF(AND(TODAY()EDATE(AA7,12),AN7="Ref erred to TX"),IF(AL7="","Eval Before RF",

The string below (which attaches to my "beasty" formula above) has been
separated out from the above formula for the purpose of clarity in this
posting.

It seems to work appropriately (creating the Confirm AC Follow Through Flag)
ONLY when a date is input into cell AA7. However, the string of formula below
should ALSO "register" and produce the applicable flag (Confirm AC Follow
Through), when a date input into cell AL7 (and NO date is placed in cell AA7).

IF(AND(TODAY()EDATE(AL7,6),AN7="Referred to Tx"),"Confirm AC Follow
Through","")),"")),

This last string of the formula (which comes after that which you see
above) does work, and flags according to the logic.

IF(AL7<"",IF(AND(TODAY()EDATE(AL7,12),AN7="Refer red to Tx"),"New Eval
Needed",""),"")))

So now that I almost have it, any final suggestions to tame this beast? I
suspect that I need to add in one more piece of logic in order for the
formula to work 100%. Again, it is only the string I pointed out (under the
single condition mentioned) that doesn't seem to want to play :(

Thanks very much for any help offered!
HAPPY 4th of July all!

Dan

Thank you for the suggestion BJ. I will try that.

Dan

"bj" wrote:

I think part of the problem is that you can only have 7 levels of embedded if
statements.

one method to get around this is to use choose() if you have 29 or fewer
options
=choose((AND(TODAY()EDATE(AL7,12))+2*AND(TODAY()
AG7,AN7="AC Initiated")+3*...,"Updated Eval Needed Before New RF","Check AC Status", ...)
You will have to take care that your options are not duplicated and are
appropriate for the choose sselection such that onely one will be correct at
any given time.


"Dan the Man" wrote:

The portion of my "beasty" formula that you see below works well.

=IF(AND(TODAY()EDATE(AL7,12),AA7<"",AL7<"",AN7= "Referred to Tx"),"Updated
Eval Needed Before New RF",IF(OR(AA7<"",AND(TODAY(
AG7,AN7="AC Initiated")),IF(AND(TODAY()AG7,AN7="AC Initiated"),IF(AP7="","Check AC Status",IF(AND(TODAY()EDATE(AP7,6),AN7="AC Initiated"),"Re-Check AC Status","")),IF(AND(TODAY()EDATE(AA7,12),AN7="Ref erred to TX"),IF(AL7="","Eval Before RF",

The string below (which attaches to my "beasty" formula above) has been
separated out from the above formula for the purpose of clarity in this
posting.

It seems to work appropriately (creating the Confirm AC Follow Through Flag)
ONLY when a date is input into cell AA7. However, the string of formula below
should ALSO "register" and produce the applicable flag (Confirm AC Follow
Through), when a date input into cell AL7 (and NO date is placed in cell AA7).

IF(AND(TODAY()EDATE(AL7,6),AN7="Referred to Tx"),"Confirm AC Follow
Through","")),"")),

This last string of the formula (which comes after that which you see
above) does work, and flags according to the logic.

IF(AL7<"",IF(AND(TODAY()EDATE(AL7,12),AN7="Refer red to Tx"),"New Eval
Needed",""),"")))

So now that I almost have it, any final suggestions to tame this beast? I
suspect that I need to add in one more piece of logic in order for the
formula to work 100%. Again, it is only the string I pointed out (under the
single condition mentioned) that doesn't seem to want to play :(

Thanks very much for any help offered!
HAPPY 4th of July all!

Dan