Thanks guyz!
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="Referred to
TX"),IF(AL7="","Eval Before RF",
The string below doesn't seem to register during testing, however it does
not create any error messages when incorporated. It is as if I never included
this string.
IF(AND(TODAY()>EDATE(AL7,6),AN7="Referred to Tx"),"Confirm AC Follow
Through","")),"")),
This last string does work, and flags according to the logic.
IF(AL7<>"",IF(AND(TODAY()>EDATE(AL7,12),AN7="Referred to Tx"),"New Eval
Needed",""),"")))
So now that I almost have it, any final suggestions to tame this beast?
Thanks,
Dan
"Pete_UK" wrote:
> Following on from Driller's suggestion, I would suggest that you
> arrange your IFs like so:
>
> =IF(criteria1=true,"result1_if_true","") &
> IF(criteria2=true,"result2_if_true","") &
> IF(criteria3=true,"result3_if_true","") &
> IF(criteria4=true,"result4_if_true","") &
> IF(criteria5=true,"result5_if_true","") &
> IF(criteria6=true,"result6_if_true","") &
> IF(criteria7=true,"result7_if_true","")
>
> The criteria should be in priority order, and mutually exclusive. This
> way you can string along many more IFs than the limit of 7 nested IFs
> as none of them are nested, and if you end up with a blank cell then
> there has not been any criteria which has been met.
>
> Hope this helps.
>
> Pete
>
>
> On Jul 4, 12:28 am, driller <dril...@discussions.microsoft.com> wrote:
> > Dan The Man,
> >
> > from the beasty formula you have...
> >
> > from the first *if*
> > =IF(AND(TODAY()>EDATE(AL7,12),AA7<>"",AL7<>"",AN7="Referred to Tx"),"Updated
> > Eval Needed Before New RF",............
> > ***i try to evaluate the result by using the tools>formula
> > auditing>evaluate...
> > i found out that the evaluate tool box bear a result of ***#NAME?****..
> > Yet the cell result shows as what u may expect without error result....
> >
> > from the new addl *if*which u say can stand alone in one cell...i agree as
> > per my test..
> > =IF(AND(TODAY()>EDATE(AL7,12),AA7="",AL7<>"",AN7="Referred to Tx"),"New Eval
> > Needed",......
> >
> > THEN
> > when i try to append the new addl. if as the first part of the formula, like
> > this...
> >
> > =IF(AND(TODAY()>EDATE(AL7,12),AA7="",AL7<>"",AN7="Referred to Tx"),"New Eval
> > Needed",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(AQ7="","Check AC
> > Status",IF(AND(TODAY()>EDATE(AQ7,6),AN7="AC Initiated"),"Re-Check AC
> > Status","")),IF(AND(TODAY()>EDATE(AA7,12),AN7="Referred to
> > TX"),IF(AL7="","Eval Before RF",IF(AND(TODAY()>EDATE(AL7,6),AN7="Referred to
> > Tx"),"Confirm AC Follow
> > Through","")),"")),IF(AL7<>"",IF(AND(TODAY()>EDATE(AL7,6),AN7="Referred to
> > Tx"),"Confirm AC Follow Through",""),""))))
> >
> > and then when i try to *enter* , a pop-up appear saying *formula error* and
> > the *today()* inside the 3rd from the last *if* had been highlighted...
> >
> > there may be a twist in your beast of formula, but i suggest to formulate
> > your multi-ifs with something like this...with priority result in the first
> > line of *if*..
> >
> > =IF(criteria1=true,"result1_if_true",IF(criteria2=true,"result2_if_true",IF-(criteria3=true,"result3_if_true",IF(criteria4=true,"result4_if_true",IF(cr-iteria5=true,"result5_if_true",IF(criteria6=true,"result6_if_true",IF(crite-ria7=true,"result7_if_true","RESULT IF NO CRITERIA WAS TRUE"))))))
> >
> > regards,
> > driller
> > --
> > *****
> > birds of the same feather flock together..
> >
> >
> >
> > "Dan the Man" wrote:
> > > Previously I received wonderful help with a very long piece of formula, and
> > > that which you see below works like a charm!
> >
> > > =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(AQ7="","Check AC
> > > Status",IF(AND(TODAY()>EDATE(AQ7,6),AN7="AC Initiated"),"Re-Check AC
> > > Status","")),IF(AND(TODAY()>EDATE(AA7,12),AN7="Referred to
> > > TX"),IF(AL7="","Eval Before RF",IF(AND(TODAY()>EDATE(AL7,6),AN7="Referred to
> > > Tx"),"Confirm AC Follow
> > > Through","")),"")),IF(AL7<>"",IF(AND(TODAY()>EDATE(AL7,6),AN7="Referred to
> > > Tx"),"Confirm AC Follow Through",""),"")))
> >
> > > I have to add in one last line to accomplish the final requirement, and
> > > hopefully someone can help me. When I attempt to incorporate the information
> > > it doesn't work, however if I put the information into its own stand alone
> > > cell it works just fine. Here is the additional piece of formula I'd like to
> > > weave into the above "beast":
> >
> > > =IF(AND(TODAY()>EDATE(AL7,12),AA7="",AL7<>"",AN7="Referred to Tx"),"New Eval
> > > Needed","")
> >
> > > Not sure if this is enough information for you all, or if you more details
> > > about the "requirement" surrounding the logic.
> >
> > > Thanks in advance!
> >
> > > Dan- Hide quoted text -
> >
> > - Show quoted text -
>
>
>
Similar Threads
