|
If Max or Ron can answer this they are Gods!
Hey Guyz!
Since Max and Ron have been so helpful, I'll throw out a more complex formula. My best friend is a senior software engineer, however since he's got married, he hasn't had a lot of time for my spreadsheet problems. He created the spreadsheet for me, but I've been left to my own devices since he's taken his wife. Here is another formula I am working with (which works): =IF(OR(AA7<"",OR(AND(TODAY()AG7,(AN7="AC Initiated")))),IF(AND(TODAY()AG7,(AN7="AC Initiated")),IF(AP7="","Check AC Status",IF(AND(TODAY()(EDATE(AP7,6)),OR(AN7="AC Initiated")),"Re-Check AC Status","")),IF(AND(TODAY()EDATE(AA7,12),OR(AN7=" Referred to TX")),IF(AL7="","Eval Before RF",IF(AND(TODAY()(EDATE(AL7,6)),OR(AN7="Referr ed to Tx")),"Confirm AC Follow Through","")),"")),IF(AL7<"",IF(AND(TODAY()EDATE (AL7,6),OR(AN7="Referred to Tx")),"Confirm AC Follow Through",""),"")) The following are additional requirements I want to encorporate into the above "beast of a formula": A flag entitled "Update Eval Before Hearing" should appear if more than a year elapses (from the date that the evaluation was posted in Row AL). To clarify, the "Confirm AC Follow Through" flag appears if more than 6 months , but less than 12 months has elapsed (which the above formula does without difficulty), and the new "Update Eval Before Hearing", should appear if more than 1 year elapses. The addition to the formula also presumes that a Hearing date was posted in Row AA. I tried to integrate the following piece of logic into the above formula (to address the above requirement), but it didn't work: IF(AND(TODAY()(EDATE(AL7,12)),OR(AN7="Referred to Tx")),IF(AA7<"","Update Eval Before Hearing","")),"")) I realize that this is a very long and detailed formula, so I understand if it's asking too much............ Happy 4th! Dan |
If Max or Ron can answer this they are Gods!
Without studying your formula in any detail, and based solely on your
description of the addition you want and the fact that the existing formula works correctly, I would guess that putting this (don't miss the comma at the end)... IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing", in front of the formula you now have (that is, after the equal sign but before the first IF) and placing a closing parenthesis at the end of your existing formula, would do what you want. Rick "Dan the Man" wrote in message ... Hey Guyz! Since Max and Ron have been so helpful, I'll throw out a more complex formula. My best friend is a senior software engineer, however since he's got married, he hasn't had a lot of time for my spreadsheet problems. He created the spreadsheet for me, but I've been left to my own devices since he's taken his wife. Here is another formula I am working with (which works): =IF(OR(AA7<"",OR(AND(TODAY()AG7,(AN7="AC Initiated")))),IF(AND(TODAY()AG7,(AN7="AC Initiated")),IF(AP7="","Check AC Status",IF(AND(TODAY()(EDATE(AP7,6)),OR(AN7="AC Initiated")),"Re-Check AC Status","")),IF(AND(TODAY()EDATE(AA7,12),OR(AN7=" Referred to TX")),IF(AL7="","Eval Before RF",IF(AND(TODAY()(EDATE(AL7,6)),OR(AN7="Referr ed to Tx")),"Confirm AC Follow Through","")),"")),IF(AL7<"",IF(AND(TODAY()EDATE (AL7,6),OR(AN7="Referred to Tx")),"Confirm AC Follow Through",""),"")) The following are additional requirements I want to encorporate into the above "beast of a formula": A flag entitled "Update Eval Before Hearing" should appear if more than a year elapses (from the date that the evaluation was posted in Row AL). To clarify, the "Confirm AC Follow Through" flag appears if more than 6 months , but less than 12 months has elapsed (which the above formula does without difficulty), and the new "Update Eval Before Hearing", should appear if more than 1 year elapses. The addition to the formula also presumes that a Hearing date was posted in Row AA. I tried to integrate the following piece of logic into the above formula (to address the above requirement), but it didn't work: IF(AND(TODAY()(EDATE(AL7,12)),OR(AN7="Referred to Tx")),IF(AA7<"","Update Eval Before Hearing","")),"")) I realize that this is a very long and detailed formula, so I understand if it's asking too much............ Happy 4th! Dan |
If Max or Ron can answer this they are Gods!
Thanks Rick. That got me closer, but still not 100%. With your suggestion,
the "Update Eval Before Hearing" flag does appear if the date in Cell AL7 is greater than 12 months, however if I enter a date under 6 months, the flag "Confirm AC follow Through" still shows up. That flag should only appear (per the formula below) if more than 6 months, but less than 12 months has elapsed. I appreciate your suggestion. Dan "Rick Rothstein (MVP - VB)" wrote: Without studying your formula in any detail, and based solely on your description of the addition you want and the fact that the existing formula works correctly, I would guess that putting this (don't miss the comma at the end)... IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing", in front of the formula you now have (that is, after the equal sign but before the first IF) and placing a closing parenthesis at the end of your existing formula, would do what you want. Rick "Dan the Man" wrote in message ... Hey Guyz! Since Max and Ron have been so helpful, I'll throw out a more complex formula. My best friend is a senior software engineer, however since he's got married, he hasn't had a lot of time for my spreadsheet problems. He created the spreadsheet for me, but I've been left to my own devices since he's taken his wife. Here is another formula I am working with (which works): =IF(OR(AA7<"",OR(AND(TODAY()AG7,(AN7="AC Initiated")))),IF(AND(TODAY()AG7,(AN7="AC Initiated")),IF(AP7="","Check AC Status",IF(AND(TODAY()(EDATE(AP7,6)),OR(AN7="AC Initiated")),"Re-Check AC Status","")),IF(AND(TODAY()EDATE(AA7,12),OR(AN7=" Referred to TX")),IF(AL7="","Eval Before RF",IF(AND(TODAY()(EDATE(AL7,6)),OR(AN7="Referr ed to Tx")),"Confirm AC Follow Through","")),"")),IF(AL7<"",IF(AND(TODAY()EDATE (AL7,6),OR(AN7="Referred to Tx")),"Confirm AC Follow Through",""),"")) The following are additional requirements I want to encorporate into the above "beast of a formula": A flag entitled "Update Eval Before Hearing" should appear if more than a year elapses (from the date that the evaluation was posted in Row AL). To clarify, the "Confirm AC Follow Through" flag appears if more than 6 months , but less than 12 months has elapsed (which the above formula does without difficulty), and the new "Update Eval Before Hearing", should appear if more than 1 year elapses. The addition to the formula also presumes that a Hearing date was posted in Row AA. I tried to integrate the following piece of logic into the above formula (to address the above requirement), but it didn't work: IF(AND(TODAY()(EDATE(AL7,12)),OR(AN7="Referred to Tx")),IF(AA7<"","Update Eval Before Hearing","")),"")) I realize that this is a very long and detailed formula, so I understand if it's asking too much............ Happy 4th! Dan |
If Max or Ron can answer this they are Gods!
Sorry, I apparently misread your 2nd "large" paragraph as indicating your
existing formula worked as expected accept for the greater than one year problem. We should be able to handle the less than six months problem with another IF function (I think<g). Instead of what I suggested earlier, try putting this... IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing",IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through", in front of the **ORIGINAL** formula you had (that is, after the equal sign but before the first IF) and put TWO closing parentheses at the end of that original formula. Now, there is a chance that you will have some non-essential testing conditions left over inside that original formula (I'm not 100% sure) as a result of this, but it shouldn't matter... the only dates it will have to process are those where AL is less than 6 months (any other condition testing for a larger AL date, if in there, will not activate). Rick "Dan the Man" wrote in message ... Thanks Rick. That got me closer, but still not 100%. With your suggestion, the "Update Eval Before Hearing" flag does appear if the date in Cell AL7 is greater than 12 months, however if I enter a date under 6 months, the flag "Confirm AC follow Through" still shows up. That flag should only appear (per the formula below) if more than 6 months, but less than 12 months has elapsed. I appreciate your suggestion. Dan "Rick Rothstein (MVP - VB)" wrote: Without studying your formula in any detail, and based solely on your description of the addition you want and the fact that the existing formula works correctly, I would guess that putting this (don't miss the comma at the end)... IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing", in front of the formula you now have (that is, after the equal sign but before the first IF) and placing a closing parenthesis at the end of your existing formula, would do what you want. Rick "Dan the Man" wrote in message ... Hey Guyz! Since Max and Ron have been so helpful, I'll throw out a more complex formula. My best friend is a senior software engineer, however since he's got married, he hasn't had a lot of time for my spreadsheet problems. He created the spreadsheet for me, but I've been left to my own devices since he's taken his wife. Here is another formula I am working with (which works): =IF(OR(AA7<"",OR(AND(TODAY()AG7,(AN7="AC Initiated")))),IF(AND(TODAY()AG7,(AN7="AC Initiated")),IF(AP7="","Check AC Status",IF(AND(TODAY()(EDATE(AP7,6)),OR(AN7="AC Initiated")),"Re-Check AC Status","")),IF(AND(TODAY()EDATE(AA7,12),OR(AN7=" Referred to TX")),IF(AL7="","Eval Before RF",IF(AND(TODAY()(EDATE(AL7,6)),OR(AN7="Referr ed to Tx")),"Confirm AC Follow Through","")),"")),IF(AL7<"",IF(AND(TODAY()EDATE (AL7,6),OR(AN7="Referred to Tx")),"Confirm AC Follow Through",""),"")) The following are additional requirements I want to encorporate into the above "beast of a formula": A flag entitled "Update Eval Before Hearing" should appear if more than a year elapses (from the date that the evaluation was posted in Row AL). To clarify, the "Confirm AC Follow Through" flag appears if more than 6 months , but less than 12 months has elapsed (which the above formula does without difficulty), and the new "Update Eval Before Hearing", should appear if more than 1 year elapses. The addition to the formula also presumes that a Hearing date was posted in Row AA. I tried to integrate the following piece of logic into the above formula (to address the above requirement), but it didn't work: IF(AND(TODAY()(EDATE(AL7,12)),OR(AN7="Referred to Tx")),IF(AA7<"","Update Eval Before Hearing","")),"")) I realize that this is a very long and detailed formula, so I understand if it's asking too much............ Happy 4th! Dan |
If Max or Ron can answer this they are Gods!
I think I read your instructions correctly Rick, but Excel didn't seem to
like the additions you gave me (Excel can be so testy at times, lol). I separated out your suggestion so you can see it better. I also added the two additional parenthesis at the end of the entire formula..........Dan =IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing",IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through")), IF(OR(AA7<"",OR(AND(TODAY()AG7,(AN7="AC Initiated")))),IF(AND(TODAY()AG7,(AN7="AC Initiated")),IF(AP7="","Check AC Status",IF(AND(TODAY()(EDATE(AP7,6)),OR(AN7="AC Initiated")),"Re-Check AC Status","")),IF(AND(TODAY()EDATE(AA7,12),OR(AN7=" Referred to TX")),IF(AL7="","Eval Before RF",IF(AND(TODAY()(EDATE(AL7,6)),OR(AN7="Referr ed to Tx")),"Confirm AC Follow Through","")),"")),IF(AL7<"",IF(AND(TODAY()EDATE (AL7,6),OR(AN7="Referred to Tx")),"Confirm AC Follow Through",""),"")))) "Rick Rothstein (MVP - VB)" wrote: Sorry, I apparently misread your 2nd "large" paragraph as indicating your existing formula worked as expected accept for the greater than one year problem. We should be able to handle the less than six months problem with another IF function (I think<g). Instead of what I suggested earlier, try putting this... IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing",IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through", in front of the **ORIGINAL** formula you had (that is, after the equal sign but before the first IF) and put TWO closing parentheses at the end of that original formula. Now, there is a chance that you will have some non-essential testing conditions left over inside that original formula (I'm not 100% sure) as a result of this, but it shouldn't matter... the only dates it will have to process are those where AL is less than 6 months (any other condition testing for a larger AL date, if in there, will not activate). Rick "Dan the Man" wrote in message ... Thanks Rick. That got me closer, but still not 100%. With your suggestion, the "Update Eval Before Hearing" flag does appear if the date in Cell AL7 is greater than 12 months, however if I enter a date under 6 months, the flag "Confirm AC follow Through" still shows up. That flag should only appear (per the formula below) if more than 6 months, but less than 12 months has elapsed. I appreciate your suggestion. Dan "Rick Rothstein (MVP - VB)" wrote: Without studying your formula in any detail, and based solely on your description of the addition you want and the fact that the existing formula works correctly, I would guess that putting this (don't miss the comma at the end)... IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing", in front of the formula you now have (that is, after the equal sign but before the first IF) and placing a closing parenthesis at the end of your existing formula, would do what you want. Rick "Dan the Man" wrote in message ... Hey Guyz! Since Max and Ron have been so helpful, I'll throw out a more complex formula. My best friend is a senior software engineer, however since he's got married, he hasn't had a lot of time for my spreadsheet problems. He created the spreadsheet for me, but I've been left to my own devices since he's taken his wife. Here is another formula I am working with (which works): =IF(OR(AA7<"",OR(AND(TODAY()AG7,(AN7="AC Initiated")))),IF(AND(TODAY()AG7,(AN7="AC Initiated")),IF(AP7="","Check AC Status",IF(AND(TODAY()(EDATE(AP7,6)),OR(AN7="AC Initiated")),"Re-Check AC Status","")),IF(AND(TODAY()EDATE(AA7,12),OR(AN7=" Referred to TX")),IF(AL7="","Eval Before RF",IF(AND(TODAY()(EDATE(AL7,6)),OR(AN7="Referr ed to Tx")),"Confirm AC Follow Through","")),"")),IF(AL7<"",IF(AND(TODAY()EDATE (AL7,6),OR(AN7="Referred to Tx")),"Confirm AC Follow Through",""),"")) The following are additional requirements I want to encorporate into the above "beast of a formula": A flag entitled "Update Eval Before Hearing" should appear if more than a year elapses (from the date that the evaluation was posted in Row AL). To clarify, the "Confirm AC Follow Through" flag appears if more than 6 months , but less than 12 months has elapsed (which the above formula does without difficulty), and the new "Update Eval Before Hearing", should appear if more than 1 year elapses. The addition to the formula also presumes that a Hearing date was posted in Row AA. I tried to integrate the following piece of logic into the above formula (to address the above requirement), but it didn't work: IF(AND(TODAY()(EDATE(AL7,12)),OR(AN7="Referred to Tx")),IF(AA7<"","Update Eval Before Hearing","")),"")) I realize that this is a very long and detailed formula, so I understand if it's asking too much............ Happy 4th! Dan |
If Max or Ron can answer this they are Gods!
See the single inline comment....
"Dan the Man" wrote in message ... I think I read your instructions correctly Rick, but Excel didn't seem to like the additions you gave me (Excel can be so testy at times, lol). I separated out your suggestion so you can see it better. I also added the two additional parenthesis at the end of the entire formula..........Dan =IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing",IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through")), You added two parentheses to the end of the above that I did not ask for (the two you added at the end of the original formula below are correct, those I asked for). =IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing",IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through", Remove them and see if that makes a difference. Rick IF(OR(AA7<"",OR(AND(TODAY()AG7,(AN7="AC Initiated")))),IF(AND(TODAY()AG7,(AN7="AC Initiated")),IF(AP7="","Check AC Status",IF(AND(TODAY()(EDATE(AP7,6)),OR(AN7="AC Initiated")),"Re-Check AC Status","")),IF(AND(TODAY()EDATE(AA7,12),OR(AN7=" Referred to TX")),IF(AL7="","Eval Before RF",IF(AND(TODAY()(EDATE(AL7,6)),OR(AN7="Referr ed to Tx")),"Confirm AC Follow Through","")),"")),IF(AL7<"",IF(AND(TODAY()EDATE (AL7,6),OR(AN7="Referred to Tx")),"Confirm AC Follow Through",""),"")))) "Rick Rothstein (MVP - VB)" wrote: Sorry, I apparently misread your 2nd "large" paragraph as indicating your existing formula worked as expected accept for the greater than one year problem. We should be able to handle the less than six months problem with another IF function (I think<g). Instead of what I suggested earlier, try putting this... IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing",IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through", in front of the **ORIGINAL** formula you had (that is, after the equal sign but before the first IF) and put TWO closing parentheses at the end of that original formula. Now, there is a chance that you will have some non-essential testing conditions left over inside that original formula (I'm not 100% sure) as a result of this, but it shouldn't matter... the only dates it will have to process are those where AL is less than 6 months (any other condition testing for a larger AL date, if in there, will not activate). Rick "Dan the Man" wrote in message ... Thanks Rick. That got me closer, but still not 100%. With your suggestion, the "Update Eval Before Hearing" flag does appear if the date in Cell AL7 is greater than 12 months, however if I enter a date under 6 months, the flag "Confirm AC follow Through" still shows up. That flag should only appear (per the formula below) if more than 6 months, but less than 12 months has elapsed. I appreciate your suggestion. Dan "Rick Rothstein (MVP - VB)" wrote: Without studying your formula in any detail, and based solely on your description of the addition you want and the fact that the existing formula works correctly, I would guess that putting this (don't miss the comma at the end)... IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing", in front of the formula you now have (that is, after the equal sign but before the first IF) and placing a closing parenthesis at the end of your existing formula, would do what you want. Rick "Dan the Man" wrote in message ... Hey Guyz! Since Max and Ron have been so helpful, I'll throw out a more complex formula. My best friend is a senior software engineer, however since he's got married, he hasn't had a lot of time for my spreadsheet problems. He created the spreadsheet for me, but I've been left to my own devices since he's taken his wife. Here is another formula I am working with (which works): =IF(OR(AA7<"",OR(AND(TODAY()AG7,(AN7="AC Initiated")))),IF(AND(TODAY()AG7,(AN7="AC Initiated")),IF(AP7="","Check AC Status",IF(AND(TODAY()(EDATE(AP7,6)),OR(AN7="AC Initiated")),"Re-Check AC Status","")),IF(AND(TODAY()EDATE(AA7,12),OR(AN7=" Referred to TX")),IF(AL7="","Eval Before RF",IF(AND(TODAY()(EDATE(AL7,6)),OR(AN7="Referr ed to Tx")),"Confirm AC Follow Through","")),"")),IF(AL7<"",IF(AND(TODAY()EDATE (AL7,6),OR(AN7="Referred to Tx")),"Confirm AC Follow Through",""),"")) The following are additional requirements I want to encorporate into the above "beast of a formula": A flag entitled "Update Eval Before Hearing" should appear if more than a year elapses (from the date that the evaluation was posted in Row AL). To clarify, the "Confirm AC Follow Through" flag appears if more than 6 months , but less than 12 months has elapsed (which the above formula does without difficulty), and the new "Update Eval Before Hearing", should appear if more than 1 year elapses. The addition to the formula also presumes that a Hearing date was posted in Row AA. I tried to integrate the following piece of logic into the above formula (to address the above requirement), but it didn't work: IF(AND(TODAY()(EDATE(AL7,12)),OR(AN7="Referred to Tx")),IF(AA7<"","Update Eval Before Hearing","")),"")) I realize that this is a very long and detailed formula, so I understand if it's asking too much............ Happy 4th! Dan |
If Max or Ron can answer this they are Gods!
Dan,
The formula contains five OR functions that contain only one argument and thus are superfluous. There are also a few instances of unnecessary parentheses which only add to the complexity. The nested IF structure also appears to be poorly constructed because condtions get tested more than once at different nesting levels. Unnecessary OR function and unnecessary parentheses example: OR(AND(TODAY()AG7,(AN7="AC Initiated"))) Example of condtion(s) being tested more than once at different nesting levels. Note how [ Today()AG7 ] and [ AN7="AC Initiated" ] is tested twice: =IF(OR(AA7<"",OR(AND(TODAY()AG7,(AN7="AC Initiated")))),IF(AND(TODAY()AG7,(AN7="AC Initiated"))... The OR function is allowed to have only one argument and will return True if the arguement is true, but this serves no purpose. The argument by itself achieves the same. The following is a simplification that removes unnecessary OR functions and parentheses. It is NOT an answer to your question: =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",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",""),"")) If Rick is not successful at solving your problem, then I suggest you describe a theoretical (simplified) worksheet layout that models the actual but is as simple as you can make it. Also provide example data and describe what you want to achieve. Alternatively, make the workbook available. Greg "Dan the Man" wrote: Hey Guyz! Since Max and Ron have been so helpful, I'll throw out a more complex formula. My best friend is a senior software engineer, however since he's got married, he hasn't had a lot of time for my spreadsheet problems. He created the spreadsheet for me, but I've been left to my own devices since he's taken his wife. Here is another formula I am working with (which works): =IF(OR(AA7<"",OR(AND(TODAY()AG7,(AN7="AC Initiated")))),IF(AND(TODAY()AG7,(AN7="AC Initiated")),IF(AP7="","Check AC Status",IF(AND(TODAY()(EDATE(AP7,6)),OR(AN7="AC Initiated")),"Re-Check AC Status","")),IF(AND(TODAY()EDATE(AA7,12),OR(AN7=" Referred to TX")),IF(AL7="","Eval Before RF",IF(AND(TODAY()(EDATE(AL7,6)),OR(AN7="Referr ed to Tx")),"Confirm AC Follow Through","")),"")),IF(AL7<"",IF(AND(TODAY()EDATE (AL7,6),OR(AN7="Referred to Tx")),"Confirm AC Follow Through",""),"")) The following are additional requirements I want to encorporate into the above "beast of a formula": A flag entitled "Update Eval Before Hearing" should appear if more than a year elapses (from the date that the evaluation was posted in Row AL). To clarify, the "Confirm AC Follow Through" flag appears if more than 6 months , but less than 12 months has elapsed (which the above formula does without difficulty), and the new "Update Eval Before Hearing", should appear if more than 1 year elapses. The addition to the formula also presumes that a Hearing date was posted in Row AA. I tried to integrate the following piece of logic into the above formula (to address the above requirement), but it didn't work: IF(AND(TODAY()(EDATE(AL7,12)),OR(AN7="Referred to Tx")),IF(AA7<"","Update Eval Before Hearing","")),"")) I realize that this is a very long and detailed formula, so I understand if it's asking too much............ Happy 4th! Dan |
If Max or Ron can answer this they are Gods!
I altered the formula per your suggestion below Rick, but alas no luck. I
appreciate your time very much. I'll keep at it. I will also look at Greg's suggested with respect to the "nested" IF statements and cleaning the current formula up a bit. Dan =IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing",IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through",IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing",IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through",IF(OR(AA7<"",OR(AND(TODAY()AG7,(AN7="AC Initiated")))),IF(AND(TODAY()AG7,(AN7="AC Initiated")),IF(AP7="","Check AC Status",IF(AND(TODAY()(EDATE(AP7,6)),OR(AN7="AC Initiated")),"Re-Check AC Status","")),IF(AND(TODAY()EDATE(AA7,12),OR(AN7=" Referred to TX")),IF(AL7="","Eval Before RF",IF(AND(TODAY()(EDATE(AL7,6)),OR(AN7="Referr ed to Tx")),"Confirm AC Follow Through","")),"")),IF(AL7<"",IF(AND(TODAY()EDATE (AL7,6),OR(AN7="Referred to Tx")),"Confirm AC Follow Through",""),"")))) "Rick Rothstein (MVP - VB)" wrote: See the single inline comment.... "Dan the Man" wrote in message ... I think I read your instructions correctly Rick, but Excel didn't seem to like the additions you gave me (Excel can be so testy at times, lol). I separated out your suggestion so you can see it better. I also added the two additional parenthesis at the end of the entire formula..........Dan =IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing",IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through")), You added two parentheses to the end of the above that I did not ask for (the two you added at the end of the original formula below are correct, those I asked for). =IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing",IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through", Remove them and see if that makes a difference. Rick IF(OR(AA7<"",OR(AND(TODAY()AG7,(AN7="AC Initiated")))),IF(AND(TODAY()AG7,(AN7="AC Initiated")),IF(AP7="","Check AC Status",IF(AND(TODAY()(EDATE(AP7,6)),OR(AN7="AC Initiated")),"Re-Check AC Status","")),IF(AND(TODAY()EDATE(AA7,12),OR(AN7=" Referred to TX")),IF(AL7="","Eval Before RF",IF(AND(TODAY()(EDATE(AL7,6)),OR(AN7="Referr ed to Tx")),"Confirm AC Follow Through","")),"")),IF(AL7<"",IF(AND(TODAY()EDATE (AL7,6),OR(AN7="Referred to Tx")),"Confirm AC Follow Through",""),"")))) "Rick Rothstein (MVP - VB)" wrote: Sorry, I apparently misread your 2nd "large" paragraph as indicating your existing formula worked as expected accept for the greater than one year problem. We should be able to handle the less than six months problem with another IF function (I think<g). Instead of what I suggested earlier, try putting this... IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing",IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through", in front of the **ORIGINAL** formula you had (that is, after the equal sign but before the first IF) and put TWO closing parentheses at the end of that original formula. Now, there is a chance that you will have some non-essential testing conditions left over inside that original formula (I'm not 100% sure) as a result of this, but it shouldn't matter... the only dates it will have to process are those where AL is less than 6 months (any other condition testing for a larger AL date, if in there, will not activate). Rick "Dan the Man" wrote in message ... Thanks Rick. That got me closer, but still not 100%. With your suggestion, the "Update Eval Before Hearing" flag does appear if the date in Cell AL7 is greater than 12 months, however if I enter a date under 6 months, the flag "Confirm AC follow Through" still shows up. That flag should only appear (per the formula below) if more than 6 months, but less than 12 months has elapsed. I appreciate your suggestion. Dan "Rick Rothstein (MVP - VB)" wrote: Without studying your formula in any detail, and based solely on your description of the addition you want and the fact that the existing formula works correctly, I would guess that putting this (don't miss the comma at the end)... IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing", in front of the formula you now have (that is, after the equal sign but before the first IF) and placing a closing parenthesis at the end of your existing formula, would do what you want. Rick "Dan the Man" wrote in message ... Hey Guyz! Since Max and Ron have been so helpful, I'll throw out a more complex formula. My best friend is a senior software engineer, however since he's got married, he hasn't had a lot of time for my spreadsheet problems. He created the spreadsheet for me, but I've been left to my own devices since he's taken his wife. Here is another formula I am working with (which works): =IF(OR(AA7<"",OR(AND(TODAY()AG7,(AN7="AC Initiated")))),IF(AND(TODAY()AG7,(AN7="AC Initiated")),IF(AP7="","Check AC Status",IF(AND(TODAY()(EDATE(AP7,6)),OR(AN7="AC Initiated")),"Re-Check AC Status","")),IF(AND(TODAY()EDATE(AA7,12),OR(AN7=" Referred to TX")),IF(AL7="","Eval Before RF",IF(AND(TODAY()(EDATE(AL7,6)),OR(AN7="Referr ed to Tx")),"Confirm AC Follow Through","")),"")),IF(AL7<"",IF(AND(TODAY()EDATE (AL7,6),OR(AN7="Referred to Tx")),"Confirm AC Follow Through",""),"")) The following are additional requirements I want to encorporate into the above "beast of a formula": A flag entitled "Update Eval Before Hearing" should appear if more than a year elapses (from the date that the evaluation was posted in Row AL). To clarify, the "Confirm AC Follow Through" flag appears if more than 6 months , but less than 12 months has elapsed (which the above formula does without difficulty), and the new "Update Eval Before Hearing", should appear if more than 1 year elapses. The addition to the formula also presumes that a Hearing date was posted in Row AA. I tried to integrate the following piece of logic into the above formula (to address the above requirement), but it didn't work: IF(AND(TODAY()(EDATE(AL7,12)),OR(AN7="Referred to Tx")),IF(AA7<"","Update Eval Before Hearing","")),"")) I realize that this is a very long and detailed formula, so I understand if it's asking too much............ Happy 4th! Dan |
If Max or Ron can answer this they are Gods!
I presume you are using Excel 2007, as you have 11 IFs in the formula
- Excel 2003 and earlier will only support up to 7 nested IFs. Hope this helps. Pete On Jul 2, 10:16 am, Dan the Man wrote: I altered the formula per your suggestion below Rick, but alas no luck. I appreciate your time very much. I'll keep at it. I will also look at Greg's suggested with respect to the "nested" IF statements and cleaning the current formula up a bit. Dan =IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing",IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through",IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing",IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through",IF(OR(AA7<"",OR(AND(TODAY()AG7,(AN7="AC Initiated")))),IF(AND(TODAY()AG7,(AN7="AC Initiated")),IF(AP7="","Check AC Status",IF(AND(TODAY()(EDATE(AP7,6)),OR(AN7="AC Initiated")),"Re-Check AC Status","")),IF(AND(TODAY()EDATE(AA7,12),OR(AN7=" Referred to TX")),IF(AL7="","Eval Before RF",IF(AND(TODAY()(EDATE(AL7,6)),OR(AN7="Referr ed to Tx")),"Confirm AC Follow Through","")),"")),IF(AL7<"",IF(AND(TODAY()EDATE (AL7,6),OR(AN7="Referred to Tx")),"Confirm AC Follow Through",""),"")))) "Rick Rothstein (MVP - VB)" wrote: See the single inline comment.... "Dan the Man" wrote in message ... I think I read your instructions correctly Rick, but Excel didn't seem to like the additions you gave me (Excel can be so testy at times, lol). I separated out your suggestion so you can see it better. I also added the two additional parenthesis at the end of the entire formula..........Dan =IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing",IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through")), You added two parentheses to the end of the above that I did not ask for (the two you added at the end of the original formula below are correct, those I asked for). =IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing",IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through", Remove them and see if that makes a difference. Rick IF(OR(AA7<"",OR(AND(TODAY()AG7,(AN7="AC Initiated")))),IF(AND(TODAY()AG7,(AN7="AC Initiated")),IF(AP7="","Check AC Status",IF(AND(TODAY()(EDATE(AP7,6)),OR(AN7="AC Initiated")),"Re-Check AC Status","")),IF(AND(TODAY()EDATE(AA7,12),OR(AN7=" Referred to TX")),IF(AL7="","Eval Before RF",IF(AND(TODAY()(EDATE(AL7,6)),OR(AN7="Referr ed to Tx")),"Confirm AC Follow Through","")),"")),IF(AL7<"",IF(AND(TODAY()EDATE (AL7,6),OR(AN7="Referred to Tx")),"Confirm AC Follow Through",""),"")))) "Rick Rothstein (MVP - VB)" wrote: Sorry, I apparently misread your 2nd "large" paragraph as indicating your existing formula worked as expected accept for the greater than one year problem. We should be able to handle the less than six months problem with another IF function (I think<g). Instead of what I suggested earlier, try putting this... IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing",IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through", in front of the **ORIGINAL** formula you had (that is, after the equal sign but before the first IF) and put TWO closing parentheses at the end of that original formula. Now, there is a chance that you will have some non-essential testing conditions left over inside that original formula (I'm not 100% sure) as a result of this, but it shouldn't matter... the only dates it will have to process are those where AL is less than 6 months (any other condition testing for a larger AL date, if in there, will not activate). Rick "Dan the Man" wrote in message ... Thanks Rick. That got me closer, but still not 100%. With your suggestion, the "Update Eval Before Hearing" flag does appear if the date in Cell AL7 is greater than 12 months, however if I enter a date under 6 months, the flag "Confirm AC follow Through" still shows up. That flag should only appear (per the formula below) if more than 6 months, but less than 12 months has elapsed. I appreciate your suggestion. Dan "Rick Rothstein (MVP - VB)" wrote: Without studying your formula in any detail, and based solely on your description of the addition you want and the fact that the existing formula works correctly, I would guess that putting this (don't miss the comma at the end)... IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing", in front of the formula you now have (that is, after the equal sign but before the first IF) and placing a closing parenthesis at the end of your existing formula, would do what you want. Rick "Dan the Man" wrote in message ... Hey Guyz! Since Max and Ron have been so helpful, I'll throw out a more complex formula. My best friend is a senior software engineer, however since he's got married, he hasn't had a lot of time for my spreadsheet problems. He created the spreadsheet for me, but I've been left to my own devices since he's taken his wife. Here is another formula I am working with (which works): =IF(OR(AA7<"",OR(AND(TODAY()AG7,(AN7="AC Initiated")))),IF(AND(TODAY()AG7,(AN7="AC Initiated")),IF(AP7="","Check AC Status",IF(AND(TODAY()(EDATE(AP7,6)),OR(AN7="AC Initiated")),"Re-Check AC Status","")),IF(AND(TODAY()EDATE(AA7,12),OR(AN7=" Referred to TX")),IF(AL7="","Eval Before RF",IF(AND(TODAY()(EDATE(AL7,6)),OR(AN7="Referr ed to Tx")),"Confirm AC Follow Through","")),"")),IF(AL7<"",IF(AND(TODAY()EDATE (AL7,6),OR(AN7="Referred to Tx")),"Confirm AC Follow Through",""),"")) The following are additional requirements I want to encorporate into the above "beast of a formula": A flag entitled "Update Eval Before Hearing" should appear if more than a year elapses (from the date that the evaluation was posted in Row AL). To clarify, the "Confirm AC Follow Through" flag appears if more than 6 months , but less than 12 months has elapsed (which the above formula does without difficulty), and the new "Update Eval Before Hearing", should appear if more than 1 year elapses. The addition to the formula also presumes that a Hearing date was posted in Row AA. I tried to integrate the following piece of logic into the above formula (to address the above requirement), but it didn't work: IF(AND(TODAY()(EDATE(AL7,12)),OR(AN7="Referred to Tx")),IF(AA7<"","Update Eval Before Hearing","")),"")) I realize that this is a very long and detailed formula, so I understand if it's asking too much............ Happy 4th! Dan- Hide quoted text - - Show quoted text - |
If Max or Ron can answer this they are Gods!
Hi
Recasting your formula in the following way makes it a little easier to see what is going on = IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing", IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through", IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing", IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through", IF(OR(AA7<"",OR(AND(TODAY()AG7,(AN7="AC Initiated")))), IF(AND(TODAY()AG7,(AN7="AC Initiated")),IF(AP7="","Check AC Status", IF(AND(TODAY()(EDATE(AP7,6)),OR(AN7="AC Initiated")),"Re-Check AC Status","")), IF(AND(TODAY()EDATE(AA7,12),OR(AN7="Referred to TX")), IF(AL7="","Eval Before RF", IF(AND(TODAY()(EDATE(AL7,6)),OR(AN7="Referred to Tx")),"Confirm AC Follow Through","")),"")), IF(AL7<"", IF(AND(TODAY()EDATE(AL7,6),OR(AN7="Referred to Tx")),"Confirm AC Follow Through",""),"")))) There are 12 IF's, which will not work unless your are using XL2007. Versions before this only support 7 levels of nesting. As you can se, the first 2 lines are duplicated in lines 3 and 4 There are a lot of superfluous OR's and parentheses in your formula, and I believe it can be simplified to the following. = IF(OR(AA7<"",AND(TODAY()AG7,AN7="AC Initiated"),"", IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing", IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through", IF(AND(TODAY()AG7,AN7="AC Initiated",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",AL7=""),"Eval Before RF", IF(AND(AL7<"",TODAY()EDATE(AL7,6),AN7="Referred to Tx"),"Confirm AC Follow Through","")))))) It is difficult to follow and I am not at all certain that this is correct. However, setting it out in this format, may help you to see things more clearly and thus be able to ensure that each of the relevant conditions is met. There are ways of having more than 7 IF's by using named formulae together. Take a look at http://www.cpearson.com/excel/nested.htm -- Regards Roger Govier "Dan the Man" wrote in message ... I altered the formula per your suggestion below Rick, but alas no luck. I appreciate your time very much. I'll keep at it. I will also look at Greg's suggested with respect to the "nested" IF statements and cleaning the current formula up a bit. Dan =IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing",IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through",IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing",IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through",IF(OR(AA7<"",OR(AND(TODAY()AG7,(AN7="AC Initiated")))),IF(AND(TODAY()AG7,(AN7="AC Initiated")),IF(AP7="","Check AC Status",IF(AND(TODAY()(EDATE(AP7,6)),OR(AN7="AC Initiated")),"Re-Check AC Status","")),IF(AND(TODAY()EDATE(AA7,12),OR(AN7=" Referred to TX")),IF(AL7="","Eval Before RF",IF(AND(TODAY()(EDATE(AL7,6)),OR(AN7="Referr ed to Tx")),"Confirm AC Follow Through","")),"")),IF(AL7<"",IF(AND(TODAY()EDATE (AL7,6),OR(AN7="Referred to Tx")),"Confirm AC Follow Through",""),"")))) "Rick Rothstein (MVP - VB)" wrote: See the single inline comment.... "Dan the Man" wrote in message ... I think I read your instructions correctly Rick, but Excel didn't seem to like the additions you gave me (Excel can be so testy at times, lol). I separated out your suggestion so you can see it better. I also added the two additional parenthesis at the end of the entire formula..........Dan =IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing",IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through")), You added two parentheses to the end of the above that I did not ask for (the two you added at the end of the original formula below are correct, those I asked for). =IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing",IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through", Remove them and see if that makes a difference. Rick IF(OR(AA7<"",OR(AND(TODAY()AG7,(AN7="AC Initiated")))),IF(AND(TODAY()AG7,(AN7="AC Initiated")),IF(AP7="","Check AC Status",IF(AND(TODAY()(EDATE(AP7,6)),OR(AN7="AC Initiated")),"Re-Check AC Status","")),IF(AND(TODAY()EDATE(AA7,12),OR(AN7=" Referred to TX")),IF(AL7="","Eval Before RF",IF(AND(TODAY()(EDATE(AL7,6)),OR(AN7="Referr ed to Tx")),"Confirm AC Follow Through","")),"")),IF(AL7<"",IF(AND(TODAY()EDATE (AL7,6),OR(AN7="Referred to Tx")),"Confirm AC Follow Through",""),"")))) "Rick Rothstein (MVP - VB)" wrote: Sorry, I apparently misread your 2nd "large" paragraph as indicating your existing formula worked as expected accept for the greater than one year problem. We should be able to handle the less than six months problem with another IF function (I think<g). Instead of what I suggested earlier, try putting this... IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing",IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through", in front of the **ORIGINAL** formula you had (that is, after the equal sign but before the first IF) and put TWO closing parentheses at the end of that original formula. Now, there is a chance that you will have some non-essential testing conditions left over inside that original formula (I'm not 100% sure) as a result of this, but it shouldn't matter... the only dates it will have to process are those where AL is less than 6 months (any other condition testing for a larger AL date, if in there, will not activate). Rick "Dan the Man" wrote in message ... Thanks Rick. That got me closer, but still not 100%. With your suggestion, the "Update Eval Before Hearing" flag does appear if the date in Cell AL7 is greater than 12 months, however if I enter a date under 6 months, the flag "Confirm AC follow Through" still shows up. That flag should only appear (per the formula below) if more than 6 months, but less than 12 months has elapsed. I appreciate your suggestion. Dan "Rick Rothstein (MVP - VB)" wrote: Without studying your formula in any detail, and based solely on your description of the addition you want and the fact that the existing formula works correctly, I would guess that putting this (don't miss the comma at the end)... IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing", in front of the formula you now have (that is, after the equal sign but before the first IF) and placing a closing parenthesis at the end of your existing formula, would do what you want. Rick "Dan the Man" wrote in message ... Hey Guyz! Since Max and Ron have been so helpful, I'll throw out a more complex formula. My best friend is a senior software engineer, however since he's got married, he hasn't had a lot of time for my spreadsheet problems. He created the spreadsheet for me, but I've been left to my own devices since he's taken his wife. Here is another formula I am working with (which works): =IF(OR(AA7<"",OR(AND(TODAY()AG7,(AN7="AC Initiated")))),IF(AND(TODAY()AG7,(AN7="AC Initiated")),IF(AP7="","Check AC Status",IF(AND(TODAY()(EDATE(AP7,6)),OR(AN7="AC Initiated")),"Re-Check AC Status","")),IF(AND(TODAY()EDATE(AA7,12),OR(AN7=" Referred to TX")),IF(AL7="","Eval Before RF",IF(AND(TODAY()(EDATE(AL7,6)),OR(AN7="Referr ed to Tx")),"Confirm AC Follow Through","")),"")),IF(AL7<"",IF(AND(TODAY()EDATE (AL7,6),OR(AN7="Referred to Tx")),"Confirm AC Follow Through",""),"")) The following are additional requirements I want to encorporate into the above "beast of a formula": A flag entitled "Update Eval Before Hearing" should appear if more than a year elapses (from the date that the evaluation was posted in Row AL). To clarify, the "Confirm AC Follow Through" flag appears if more than 6 months , but less than 12 months has elapsed (which the above formula does without difficulty), and the new "Update Eval Before Hearing", should appear if more than 1 year elapses. The addition to the formula also presumes that a Hearing date was posted in Row AA. I tried to integrate the following piece of logic into the above formula (to address the above requirement), but it didn't work: IF(AND(TODAY()(EDATE(AL7,12)),OR(AN7="Referred to Tx")),IF(AA7<"","Update Eval Before Hearing","")),"")) I realize that this is a very long and detailed formula, so I understand if it's asking too much............ Happy 4th! Dan |
If Max or Ron can answer this they are Gods!
Roger, your simplified formula has 25 left parentheses and 23 right
parentheses. If we just add the missing parentheses to the end and collapse the two child functions at the 3rd nesting level of the resulting formula , the structure can be seen as follows: = IF(OR(AA7<"",AND(),"", IF())) As you can see, the resulting main parent IF function has only one argument, an OR function. This OR function has four arguments, one of which is empty parentheses, and therefore is erroneous. The formula has nine nesting levels when not collapsed and thus would require xl2007. We concur that the original formula has superfluous OR functions and unnecessary parentheses that only add to the complexity. Also, the IF structure is apparently very inefficient since conditions are repeat-tested at different nesting levels. I think the formula could be greatly simplified but requires a thorough understanding of the worksheet layout and of what is needed. After simplification, xl2007 may not be required. My conclusion remains the same as what I originally posted. Regards, Greg "Roger Govier" wrote: Hi Recasting your formula in the following way makes it a little easier to see what is going on = IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing", IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through", IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing", IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through", IF(OR(AA7<"",OR(AND(TODAY()AG7,(AN7="AC Initiated")))), IF(AND(TODAY()AG7,(AN7="AC Initiated")),IF(AP7="","Check AC Status", IF(AND(TODAY()(EDATE(AP7,6)),OR(AN7="AC Initiated")),"Re-Check AC Status","")), IF(AND(TODAY()EDATE(AA7,12),OR(AN7="Referred to TX")), IF(AL7="","Eval Before RF", IF(AND(TODAY()(EDATE(AL7,6)),OR(AN7="Referred to Tx")),"Confirm AC Follow Through","")),"")), IF(AL7<"", IF(AND(TODAY()EDATE(AL7,6),OR(AN7="Referred to Tx")),"Confirm AC Follow Through",""),"")))) There are 12 IF's, which will not work unless your are using XL2007. Versions before this only support 7 levels of nesting. As you can se, the first 2 lines are duplicated in lines 3 and 4 There are a lot of superfluous OR's and parentheses in your formula, and I believe it can be simplified to the following. = IF(OR(AA7<"",AND(TODAY()AG7,AN7="AC Initiated"),"", IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing", IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through", IF(AND(TODAY()AG7,AN7="AC Initiated",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",AL7=""),"Eval Before RF", IF(AND(AL7<"",TODAY()EDATE(AL7,6),AN7="Referred to Tx"),"Confirm AC Follow Through","")))))) It is difficult to follow and I am not at all certain that this is correct. However, setting it out in this format, may help you to see things more clearly and thus be able to ensure that each of the relevant conditions is met. There are ways of having more than 7 IF's by using named formulae together. Take a look at http://www.cpearson.com/excel/nested.htm -- Regards Roger Govier "Dan the Man" wrote in message ... I altered the formula per your suggestion below Rick, but alas no luck. I appreciate your time very much. I'll keep at it. I will also look at Greg's suggested with respect to the "nested" IF statements and cleaning the current formula up a bit. Dan =IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing",IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through",IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing",IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through",IF(OR(AA7<"",OR(AND(TODAY()AG7,(AN7="AC Initiated")))),IF(AND(TODAY()AG7,(AN7="AC Initiated")),IF(AP7="","Check AC Status",IF(AND(TODAY()(EDATE(AP7,6)),OR(AN7="AC Initiated")),"Re-Check AC Status","")),IF(AND(TODAY()EDATE(AA7,12),OR(AN7=" Referred to TX")),IF(AL7="","Eval Before RF",IF(AND(TODAY()(EDATE(AL7,6)),OR(AN7="Referr ed to Tx")),"Confirm AC Follow Through","")),"")),IF(AL7<"",IF(AND(TODAY()EDATE (AL7,6),OR(AN7="Referred to Tx")),"Confirm AC Follow Through",""),"")))) "Rick Rothstein (MVP - VB)" wrote: See the single inline comment.... "Dan the Man" wrote in message ... I think I read your instructions correctly Rick, but Excel didn't seem to like the additions you gave me (Excel can be so testy at times, lol). I separated out your suggestion so you can see it better. I also added the two additional parenthesis at the end of the entire formula..........Dan =IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing",IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through")), You added two parentheses to the end of the above that I did not ask for (the two you added at the end of the original formula below are correct, those I asked for). =IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing",IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through", Remove them and see if that makes a difference. Rick IF(OR(AA7<"",OR(AND(TODAY()AG7,(AN7="AC Initiated")))),IF(AND(TODAY()AG7,(AN7="AC Initiated")),IF(AP7="","Check AC Status",IF(AND(TODAY()(EDATE(AP7,6)),OR(AN7="AC Initiated")),"Re-Check AC Status","")),IF(AND(TODAY()EDATE(AA7,12),OR(AN7=" Referred to TX")),IF(AL7="","Eval Before RF",IF(AND(TODAY()(EDATE(AL7,6)),OR(AN7="Referr ed to Tx")),"Confirm AC Follow Through","")),"")),IF(AL7<"",IF(AND(TODAY()EDATE (AL7,6),OR(AN7="Referred to Tx")),"Confirm AC Follow Through",""),"")))) "Rick Rothstein (MVP - VB)" wrote: Sorry, I apparently misread your 2nd "large" paragraph as indicating your existing formula worked as expected accept for the greater than one year problem. We should be able to handle the less than six months problem with another IF function (I think<g). Instead of what I suggested earlier, try putting this... IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing",IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through", in front of the **ORIGINAL** formula you had (that is, after the equal sign but before the first IF) and put TWO closing parentheses at the end of that original formula. Now, there is a chance that you will have some non-essential testing conditions left over inside that original formula (I'm not 100% sure) as a result of this, but it shouldn't matter... the only dates it will have to process are those where AL is less than 6 months (any other condition testing for a larger AL date, if in there, will not activate). Rick "Dan the Man" wrote in message ... Thanks Rick. That got me closer, but still not 100%. With your suggestion, the "Update Eval Before Hearing" flag does appear if the date in Cell AL7 is greater than 12 months, however if I enter a date under 6 months, the flag "Confirm AC follow Through" still shows up. That flag should only appear (per the formula below) if more than 6 months, but less than 12 months has elapsed. I appreciate your suggestion. Dan "Rick Rothstein (MVP - VB)" wrote: Without studying your formula in any detail, and based solely on your description of the addition you want and the fact that the existing formula works correctly, I would guess that putting this (don't miss the comma at the end)... IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing", in front of the formula you now have (that is, after the equal sign but before the first IF) and placing a closing parenthesis at the end of your existing formula, would do what you want. Rick "Dan the Man" wrote in message ... Hey Guyz! Since Max and Ron have been so helpful, I'll throw out a more complex formula. My best friend is a senior software engineer, however since he's got married, he hasn't had a lot of time for my spreadsheet problems. He created the spreadsheet for me, but I've been left to my own devices since he's taken his wife. Here is another formula I am working with (which works): =IF(OR(AA7<"",OR(AND(TODAY()AG7,(AN7="AC Initiated")))),IF(AND(TODAY()AG7,(AN7="AC Initiated")),IF(AP7="","Check AC Status",IF(AND(TODAY()(EDATE(AP7,6)),OR(AN7="AC Initiated")),"Re-Check AC Status","")),IF(AND(TODAY()EDATE(AA7,12),OR(AN7=" Referred to TX")),IF(AL7="","Eval Before RF",IF(AND(TODAY()(EDATE(AL7,6)),OR(AN7="Referr ed to Tx")),"Confirm AC Follow Through","")),"")),IF(AL7<"",IF(AND(TODAY()EDATE (AL7,6),OR(AN7="Referred to Tx")),"Confirm AC Follow Through",""),"")) The following are additional requirements I want to encorporate into the above "beast of a formula": A flag entitled "Update Eval Before Hearing" should appear if more than a year elapses (from the date that the evaluation was posted in Row AL). To clarify, the "Confirm AC Follow Through" flag appears if more than 6 months , but less than 12 months has elapsed (which the above formula does without difficulty), and the new "Update Eval Before Hearing", should appear if more than 1 year elapses. The addition to the formula also presumes that a Hearing date was posted in Row AA. I tried to integrate the following piece of logic into the above formula (to address the above requirement), but it didn't work: IF(AND(TODAY()(EDATE(AL7,12)),OR(AN7="Referred to Tx")),IF(AA7<"","Update Eval Before Hearing","")),"")) I realize that this is a very long and detailed formula, so I |
If Max or Ron can answer this they are Gods!
Dan the Man,
for the beast of formula u had and the additional If formula u need,,,the status remark you need are as follows... -------------- 1) Update Eval Before Hearing, 2) Confirm AC Follow Through, 3) Re-Check AC Status, 4) Check AC Status, 5) Eval Before RF, 6) "" BLANK -------------- From the above #status, please narrate your conditions AND/OR...i have filled some conditions for your fill-in....**<=?** 1) RESULT ="Update Eval Before Hearing" -- [TODAY()EDATE(AL7,12), AA7<="", AG7<="", AL7<"", AN7="?", AP7<=""] 2) RESULT ="Confirm AC Follow Through" -- [TODAY()EDATE(AL7,6), AA7=<"", AG7=<"", AL7<"", AN7="Referred to TX", AP7=<""] 3) RESULT ="Re-Check AC Status" -- [TODAY()EDATE(AP7,6), AA7<="", AG7<="", AL7<="", AN7="AC Initiated", AP7<""] 4) RESULT ="Check AC Status" -- [TODAY()AG7, AA7<="", AG7<"", AL7<="", AN7="AC Initiated", AP7=""] 5) RESULT ="Eval Before RF" -- [TODAY()EDATE(AA7,12), AA7<"", AG7<="", AL7="", AN7="Referred to TX", AP7<=""] 6) RESULT="" *BLANK*-- [AA7<="", AG7<="", AL7<="", AN7="?", AP7<=""] Are the dates on AA7,AG7,AL7,AP7 linked together thru formulas or encoded dates.? Is there any status remark when the result needed is "completed" specially when today()<all other dates on AA7,AG7,AL7,AP7....? regards, driller -- ***** birds of the same feather flock together.. "Greg Wilson" wrote: Roger, your simplified formula has 25 left parentheses and 23 right parentheses. If we just add the missing parentheses to the end and collapse the two child functions at the 3rd nesting level of the resulting formula , the structure can be seen as follows: = IF(OR(AA7<"",AND(),"", IF())) As you can see, the resulting main parent IF function has only one argument, an OR function. This OR function has four arguments, one of which is empty parentheses, and therefore is erroneous. The formula has nine nesting levels when not collapsed and thus would require xl2007. We concur that the original formula has superfluous OR functions and unnecessary parentheses that only add to the complexity. Also, the IF structure is apparently very inefficient since conditions are repeat-tested at different nesting levels. I think the formula could be greatly simplified but requires a thorough understanding of the worksheet layout and of what is needed. After simplification, xl2007 may not be required. My conclusion remains the same as what I originally posted. Regards, Greg "Roger Govier" wrote: Hi Recasting your formula in the following way makes it a little easier to see what is going on = IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing", IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through", IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing", IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through", IF(OR(AA7<"",OR(AND(TODAY()AG7,(AN7="AC Initiated")))), IF(AND(TODAY()AG7,(AN7="AC Initiated")),IF(AP7="","Check AC Status", IF(AND(TODAY()(EDATE(AP7,6)),OR(AN7="AC Initiated")),"Re-Check AC Status","")), IF(AND(TODAY()EDATE(AA7,12),OR(AN7="Referred to TX")), IF(AL7="","Eval Before RF", IF(AND(TODAY()(EDATE(AL7,6)),OR(AN7="Referred to Tx")),"Confirm AC Follow Through","")),"")), IF(AL7<"", IF(AND(TODAY()EDATE(AL7,6),OR(AN7="Referred to Tx")),"Confirm AC Follow Through",""),"")))) There are 12 IF's, which will not work unless your are using XL2007. Versions before this only support 7 levels of nesting. As you can se, the first 2 lines are duplicated in lines 3 and 4 There are a lot of superfluous OR's and parentheses in your formula, and I believe it can be simplified to the following. = IF(OR(AA7<"",AND(TODAY()AG7,AN7="AC Initiated"),"", IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing", IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through", IF(AND(TODAY()AG7,AN7="AC Initiated",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",AL7=""),"Eval Before RF", IF(AND(AL7<"",TODAY()EDATE(AL7,6),AN7="Referred to Tx"),"Confirm AC Follow Through","")))))) It is difficult to follow and I am not at all certain that this is correct. However, setting it out in this format, may help you to see things more clearly and thus be able to ensure that each of the relevant conditions is met. There are ways of having more than 7 IF's by using named formulae together. Take a look at http://www.cpearson.com/excel/nested.htm -- Regards Roger Govier "Dan the Man" wrote in message ... I altered the formula per your suggestion below Rick, but alas no luck. I appreciate your time very much. I'll keep at it. I will also look at Greg's suggested with respect to the "nested" IF statements and cleaning the current formula up a bit. Dan =IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing",IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through",IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing",IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through",IF(OR(AA7<"",OR(AND(TODAY()AG7,(AN7="AC Initiated")))),IF(AND(TODAY()AG7,(AN7="AC Initiated")),IF(AP7="","Check AC Status",IF(AND(TODAY()(EDATE(AP7,6)),OR(AN7="AC Initiated")),"Re-Check AC Status","")),IF(AND(TODAY()EDATE(AA7,12),OR(AN7=" Referred to TX")),IF(AL7="","Eval Before RF",IF(AND(TODAY()(EDATE(AL7,6)),OR(AN7="Referr ed to Tx")),"Confirm AC Follow Through","")),"")),IF(AL7<"",IF(AND(TODAY()EDATE (AL7,6),OR(AN7="Referred to Tx")),"Confirm AC Follow Through",""),"")))) "Rick Rothstein (MVP - VB)" wrote: See the single inline comment.... "Dan the Man" wrote in message ... I think I read your instructions correctly Rick, but Excel didn't seem to like the additions you gave me (Excel can be so testy at times, lol). I separated out your suggestion so you can see it better. I also added the two additional parenthesis at the end of the entire formula..........Dan =IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing",IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through")), You added two parentheses to the end of the above that I did not ask for (the two you added at the end of the original formula below are correct, those I asked for). =IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing",IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through", Remove them and see if that makes a difference. Rick IF(OR(AA7<"",OR(AND(TODAY()AG7,(AN7="AC Initiated")))),IF(AND(TODAY()AG7,(AN7="AC Initiated")),IF(AP7="","Check AC Status",IF(AND(TODAY()(EDATE(AP7,6)),OR(AN7="AC Initiated")),"Re-Check AC Status","")),IF(AND(TODAY()EDATE(AA7,12),OR(AN7=" Referred to TX")),IF(AL7="","Eval Before RF",IF(AND(TODAY()(EDATE(AL7,6)),OR(AN7="Referr ed to Tx")),"Confirm AC Follow Through","")),"")),IF(AL7<"",IF(AND(TODAY()EDATE (AL7,6),OR(AN7="Referred to Tx")),"Confirm AC Follow Through",""),"")))) "Rick Rothstein (MVP - VB)" wrote: Sorry, I apparently misread your 2nd "large" paragraph as indicating your existing formula worked as expected accept for the greater than one year problem. We should be able to handle the less than six months problem with another IF function (I think<g). Instead of what I suggested earlier, try putting this... IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing",IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through", in front of the **ORIGINAL** formula you had (that is, after the equal sign but before the first IF) and put TWO closing parentheses at the end of that original formula. Now, there is a chance that you will have some non-essential testing conditions left over inside that original formula (I'm not 100% sure) as a result of this, but it shouldn't matter... the only dates it will have to process are those where AL is less than 6 months (any other condition testing for a larger AL date, if in there, will not activate). Rick "Dan the Man" wrote in message ... Thanks Rick. That got me closer, but still not 100%. With your suggestion, the "Update Eval Before Hearing" flag does appear if the date in Cell AL7 is greater than 12 months, however if I enter a date under 6 months, the flag "Confirm AC follow Through" still shows up. That flag should only appear (per the formula below) if more than 6 months, but less than 12 months has elapsed. I appreciate your suggestion. Dan "Rick Rothstein (MVP - VB)" wrote: Without studying your formula in any detail, and based solely on your description of the addition you want and the fact that the existing formula works correctly, I would guess that putting this (don't miss the comma at the end)... IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing", in front of the formula you now have (that is, after the equal sign but before the first IF) and placing a closing parenthesis at the end of your existing formula, would do what you want. Rick "Dan the Man" wrote in message ... Hey Guyz! Since Max and Ron have been so helpful, I'll throw out a more complex formula. My best friend is a senior software engineer, however since he's got married, he hasn't had a lot of time for my spreadsheet problems. He created the spreadsheet for me, but I've been left to my own devices since he's taken his wife. Here is another formula I am working with (which works): =IF(OR(AA7<"",OR(AND(TODAY()AG7,(AN7="AC Initiated")))),IF(AND(TODAY()AG7,(AN7="AC Initiated")),IF(AP7="","Check AC Status",IF(AND(TODAY()(EDATE(AP7,6)),OR(AN7="AC Initiated")),"Re-Check AC Status","")),IF(AND(TODAY()EDATE(AA7,12),OR(AN7=" Referred to TX")),IF(AL7="","Eval Before RF",IF(AND(TODAY()(EDATE(AL7,6)),OR(AN7="Referr ed to Tx")),"Confirm AC Follow Through","")),"")),IF(AL7<"",IF(AND(TODAY()EDATE (AL7,6),OR(AN7="Referred to Tx")),"Confirm AC Follow Through",""),"")) The following are additional requirements I want to encorporate into the above "beast of a formula": A flag entitled "Update Eval Before Hearing" should appear if more than a year elapses (from the date that the evaluation was posted in Row AL). To |
If Max or Ron can answer this they are Gods!
Hi Greg
I agree. I was not suggesting that my posting was a solution, (it was written in Notepad not Excel hence the inequality of parentheses wasn't picked up). What I was trying to do was to get the formula set out more clearly for the OP to try to understand what was going on, by putting each If statement on a separate line and trying to trim out most of the duplication. Again, I agree, that without a better knowledge of exactly what they are trying to achieve, it is difficult to post a solution. -- Regards Roger Govier "Greg Wilson" wrote in message ... Roger, your simplified formula has 25 left parentheses and 23 right parentheses. If we just add the missing parentheses to the end and collapse the two child functions at the 3rd nesting level of the resulting formula , the structure can be seen as follows: = IF(OR(AA7<"",AND(),"", IF())) As you can see, the resulting main parent IF function has only one argument, an OR function. This OR function has four arguments, one of which is empty parentheses, and therefore is erroneous. The formula has nine nesting levels when not collapsed and thus would require xl2007. We concur that the original formula has superfluous OR functions and unnecessary parentheses that only add to the complexity. Also, the IF structure is apparently very inefficient since conditions are repeat-tested at different nesting levels. I think the formula could be greatly simplified but requires a thorough understanding of the worksheet layout and of what is needed. After simplification, xl2007 may not be required. My conclusion remains the same as what I originally posted. Regards, Greg "Roger Govier" wrote: Hi Recasting your formula in the following way makes it a little easier to see what is going on = IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing", IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through", IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing", IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through", IF(OR(AA7<"",OR(AND(TODAY()AG7,(AN7="AC Initiated")))), IF(AND(TODAY()AG7,(AN7="AC Initiated")),IF(AP7="","Check AC Status", IF(AND(TODAY()(EDATE(AP7,6)),OR(AN7="AC Initiated")),"Re-Check AC Status","")), IF(AND(TODAY()EDATE(AA7,12),OR(AN7="Referred to TX")), IF(AL7="","Eval Before RF", IF(AND(TODAY()(EDATE(AL7,6)),OR(AN7="Referred to Tx")),"Confirm AC Follow Through","")),"")), IF(AL7<"", IF(AND(TODAY()EDATE(AL7,6),OR(AN7="Referred to Tx")),"Confirm AC Follow Through",""),"")))) There are 12 IF's, which will not work unless your are using XL2007. Versions before this only support 7 levels of nesting. As you can se, the first 2 lines are duplicated in lines 3 and 4 There are a lot of superfluous OR's and parentheses in your formula, and I believe it can be simplified to the following. = IF(OR(AA7<"",AND(TODAY()AG7,AN7="AC Initiated"),"", IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing", IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through", IF(AND(TODAY()AG7,AN7="AC Initiated",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",AL7=""),"Eval Before RF", IF(AND(AL7<"",TODAY()EDATE(AL7,6),AN7="Referred to Tx"),"Confirm AC Follow Through","")))))) It is difficult to follow and I am not at all certain that this is correct. However, setting it out in this format, may help you to see things more clearly and thus be able to ensure that each of the relevant conditions is met. There are ways of having more than 7 IF's by using named formulae together. Take a look at http://www.cpearson.com/excel/nested.htm -- Regards Roger Govier "Dan the Man" wrote in message ... I altered the formula per your suggestion below Rick, but alas no luck. I appreciate your time very much. I'll keep at it. I will also look at Greg's suggested with respect to the "nested" IF statements and cleaning the current formula up a bit. Dan =IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing",IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through",IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing",IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through",IF(OR(AA7<"",OR(AND(TODAY()AG7,(AN7="AC Initiated")))),IF(AND(TODAY()AG7,(AN7="AC Initiated")),IF(AP7="","Check AC Status",IF(AND(TODAY()(EDATE(AP7,6)),OR(AN7="AC Initiated")),"Re-Check AC Status","")),IF(AND(TODAY()EDATE(AA7,12),OR(AN7=" Referred to TX")),IF(AL7="","Eval Before RF",IF(AND(TODAY()(EDATE(AL7,6)),OR(AN7="Referr ed to Tx")),"Confirm AC Follow Through","")),"")),IF(AL7<"",IF(AND(TODAY()EDATE (AL7,6),OR(AN7="Referred to Tx")),"Confirm AC Follow Through",""),"")))) "Rick Rothstein (MVP - VB)" wrote: See the single inline comment.... "Dan the Man" wrote in message ... I think I read your instructions correctly Rick, but Excel didn't seem to like the additions you gave me (Excel can be so testy at times, lol). I separated out your suggestion so you can see it better. I also added the two additional parenthesis at the end of the entire formula..........Dan =IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing",IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through")), You added two parentheses to the end of the above that I did not ask for (the two you added at the end of the original formula below are correct, those I asked for). =IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing",IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through", Remove them and see if that makes a difference. Rick IF(OR(AA7<"",OR(AND(TODAY()AG7,(AN7="AC Initiated")))),IF(AND(TODAY()AG7,(AN7="AC Initiated")),IF(AP7="","Check AC Status",IF(AND(TODAY()(EDATE(AP7,6)),OR(AN7="AC Initiated")),"Re-Check AC Status","")),IF(AND(TODAY()EDATE(AA7,12),OR(AN7=" Referred to TX")),IF(AL7="","Eval Before RF",IF(AND(TODAY()(EDATE(AL7,6)),OR(AN7="Referr ed to Tx")),"Confirm AC Follow Through","")),"")),IF(AL7<"",IF(AND(TODAY()EDATE (AL7,6),OR(AN7="Referred to Tx")),"Confirm AC Follow Through",""),"")))) "Rick Rothstein (MVP - VB)" wrote: Sorry, I apparently misread your 2nd "large" paragraph as indicating your existing formula worked as expected accept for the greater than one year problem. We should be able to handle the less than six months problem with another IF function (I think<g). Instead of what I suggested earlier, try putting this... IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing",IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through", in front of the **ORIGINAL** formula you had (that is, after the equal sign but before the first IF) and put TWO closing parentheses at the end of that original formula. Now, there is a chance that you will have some non-essential testing conditions left over inside that original formula (I'm not 100% sure) as a result of this, but it shouldn't matter... the only dates it will have to process are those where AL is less than 6 months (any other condition testing for a larger AL date, if in there, will not activate). Rick "Dan the Man" wrote in message ... Thanks Rick. That got me closer, but still not 100%. With your suggestion, the "Update Eval Before Hearing" flag does appear if the date in Cell AL7 is greater than 12 months, however if I enter a date under 6 months, the flag "Confirm AC follow Through" still shows up. That flag should only appear (per the formula below) if more than 6 months, but less than 12 months has elapsed. I appreciate your suggestion. Dan "Rick Rothstein (MVP - VB)" wrote: Without studying your formula in any detail, and based solely on your description of the addition you want and the fact that the existing formula works correctly, I would guess that putting this (don't miss the comma at the end)... IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing", in front of the formula you now have (that is, after the equal sign but before the first IF) and placing a closing parenthesis at the end of your existing formula, would do what you want. Rick "Dan the Man" wrote in message ... Hey Guyz! Since Max and Ron have been so helpful, I'll throw out a more complex formula. My best friend is a senior software engineer, however since he's got married, he hasn't had a lot of time for my spreadsheet problems. He created the spreadsheet for me, but I've been left to my own devices since he's taken his wife. Here is another formula I am working with (which works): =IF(OR(AA7<"",OR(AND(TODAY()AG7,(AN7="AC Initiated")))),IF(AND(TODAY()AG7,(AN7="AC Initiated")),IF(AP7="","Check AC Status",IF(AND(TODAY()(EDATE(AP7,6)),OR(AN7="AC Initiated")),"Re-Check AC Status","")),IF(AND(TODAY()EDATE(AA7,12),OR(AN7=" Referred to TX")),IF(AL7="","Eval Before RF",IF(AND(TODAY()(EDATE(AL7,6)),OR(AN7="Referr ed to Tx")),"Confirm AC Follow Through","")),"")),IF(AL7<"",IF(AND(TODAY()EDATE (AL7,6),OR(AN7="Referred to Tx")),"Confirm AC Follow Through",""),"")) The following are additional requirements I want to encorporate into the above "beast of a formula": A flag entitled "Update Eval Before Hearing" should appear if more than a year elapses (from the date that the evaluation was posted in Row AL). To clarify, the "Confirm AC Follow Through" flag appears if more than 6 months , but less than 12 months has elapsed (which the above formula does without difficulty), and the new "Update Eval Before Hearing", should appear if more than 1 year elapses. The addition to the formula also presumes that a Hearing date was posted in Row AA. I tried to integrate the following piece of logic into the above formula (to address the above requirement), but it didn't work: IF(AND(TODAY()(EDATE(AL7,12)),OR(AN7="Referred to Tx")),IF(AA7<"","Update Eval Before Hearing","")),"")) I realize that this is a very long and detailed formula, so I |
If Max or Ron can answer this they are Gods!
Today is my birthday, and Driller and others helped me piece together
everything I needed to solve my problem. The added piece that you gave me Driller (line 1 Result) was the final solve. The formula now works just as I wanted it to. I have a few more additions I need to make to this beastly formula, but for now I am content with what you helped me to solve. I'm an Excel "newbie" who has relied primarily on trial and error to learn how to utilize the power of this program. I have Excel 2002 btw, and my formula as it stands now works BEAUTIFULLY. Isn't it funny how you can keep trying with frustration and lack of success, and all of a sudden it works. Maybe this was my birthday gift, lol? I'll come back to this group when I finish writing the requirements for the final pieces of this formula. As it stands, here is what now works: =IF(AND(TODAY()EDATE(AL7,12),AA7<"",AL7<"",AN7= "Referred to Tx"),"Updated Eval Needed",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",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",""),""))) "driller" wrote: Dan the Man, for the beast of formula u had and the additional If formula u need,,,the status remark you need are as follows... -------------- 1) Update Eval Before Hearing, 2) Confirm AC Follow Through, 3) Re-Check AC Status, 4) Check AC Status, 5) Eval Before RF, 6) "" BLANK -------------- From the above #status, please narrate your conditions AND/OR...i have filled some conditions for your fill-in....**<=?** 1) RESULT ="Update Eval Before Hearing" -- [TODAY()EDATE(AL7,12), AA7<="", AG7<="", AL7<"", AN7="?", AP7<=""] 2) RESULT ="Confirm AC Follow Through" -- [TODAY()EDATE(AL7,6), AA7=<"", AG7=<"", AL7<"", AN7="Referred to TX", AP7=<""] 3) RESULT ="Re-Check AC Status" -- [TODAY()EDATE(AP7,6), AA7<="", AG7<="", AL7<="", AN7="AC Initiated", AP7<""] 4) RESULT ="Check AC Status" -- [TODAY()AG7, AA7<="", AG7<"", AL7<="", AN7="AC Initiated", AP7=""] 5) RESULT ="Eval Before RF" -- [TODAY()EDATE(AA7,12), AA7<"", AG7<="", AL7="", AN7="Referred to TX", AP7<=""] 6) RESULT="" *BLANK*-- [AA7<="", AG7<="", AL7<="", AN7="?", AP7<=""] Are the dates on AA7,AG7,AL7,AP7 linked together thru formulas or encoded dates.? Is there any status remark when the result needed is "completed" specially when today()<all other dates on AA7,AG7,AL7,AP7....? regards, driller -- ***** birds of the same feather flock together.. "Greg Wilson" wrote: Roger, your simplified formula has 25 left parentheses and 23 right parentheses. If we just add the missing parentheses to the end and collapse the two child functions at the 3rd nesting level of the resulting formula , the structure can be seen as follows: = IF(OR(AA7<"",AND(),"", IF())) As you can see, the resulting main parent IF function has only one argument, an OR function. This OR function has four arguments, one of which is empty parentheses, and therefore is erroneous. The formula has nine nesting levels when not collapsed and thus would require xl2007. We concur that the original formula has superfluous OR functions and unnecessary parentheses that only add to the complexity. Also, the IF structure is apparently very inefficient since conditions are repeat-tested at different nesting levels. I think the formula could be greatly simplified but requires a thorough understanding of the worksheet layout and of what is needed. After simplification, xl2007 may not be required. My conclusion remains the same as what I originally posted. Regards, Greg "Roger Govier" wrote: Hi Recasting your formula in the following way makes it a little easier to see what is going on = IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing", IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through", IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing", IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through", IF(OR(AA7<"",OR(AND(TODAY()AG7,(AN7="AC Initiated")))), IF(AND(TODAY()AG7,(AN7="AC Initiated")),IF(AP7="","Check AC Status", IF(AND(TODAY()(EDATE(AP7,6)),OR(AN7="AC Initiated")),"Re-Check AC Status","")), IF(AND(TODAY()EDATE(AA7,12),OR(AN7="Referred to TX")), IF(AL7="","Eval Before RF", IF(AND(TODAY()(EDATE(AL7,6)),OR(AN7="Referred to Tx")),"Confirm AC Follow Through","")),"")), IF(AL7<"", IF(AND(TODAY()EDATE(AL7,6),OR(AN7="Referred to Tx")),"Confirm AC Follow Through",""),"")))) There are 12 IF's, which will not work unless your are using XL2007. Versions before this only support 7 levels of nesting. As you can se, the first 2 lines are duplicated in lines 3 and 4 There are a lot of superfluous OR's and parentheses in your formula, and I believe it can be simplified to the following. = IF(OR(AA7<"",AND(TODAY()AG7,AN7="AC Initiated"),"", IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing", IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through", IF(AND(TODAY()AG7,AN7="AC Initiated",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",AL7=""),"Eval Before RF", IF(AND(AL7<"",TODAY()EDATE(AL7,6),AN7="Referred to Tx"),"Confirm AC Follow Through","")))))) It is difficult to follow and I am not at all certain that this is correct. However, setting it out in this format, may help you to see things more clearly and thus be able to ensure that each of the relevant conditions is met. There are ways of having more than 7 IF's by using named formulae together. Take a look at http://www.cpearson.com/excel/nested.htm -- Regards Roger Govier "Dan the Man" wrote in message ... I altered the formula per your suggestion below Rick, but alas no luck. I appreciate your time very much. I'll keep at it. I will also look at Greg's suggested with respect to the "nested" IF statements and cleaning the current formula up a bit. Dan =IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing",IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through",IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing",IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through",IF(OR(AA7<"",OR(AND(TODAY()AG7,(AN7="AC Initiated")))),IF(AND(TODAY()AG7,(AN7="AC Initiated")),IF(AP7="","Check AC Status",IF(AND(TODAY()(EDATE(AP7,6)),OR(AN7="AC Initiated")),"Re-Check AC Status","")),IF(AND(TODAY()EDATE(AA7,12),OR(AN7=" Referred to TX")),IF(AL7="","Eval Before RF",IF(AND(TODAY()(EDATE(AL7,6)),OR(AN7="Referr ed to Tx")),"Confirm AC Follow Through","")),"")),IF(AL7<"",IF(AND(TODAY()EDATE (AL7,6),OR(AN7="Referred to Tx")),"Confirm AC Follow Through",""),"")))) "Rick Rothstein (MVP - VB)" wrote: See the single inline comment.... "Dan the Man" wrote in message ... I think I read your instructions correctly Rick, but Excel didn't seem to like the additions you gave me (Excel can be so testy at times, lol). I separated out your suggestion so you can see it better. I also added the two additional parenthesis at the end of the entire formula..........Dan =IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing",IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through")), You added two parentheses to the end of the above that I did not ask for (the two you added at the end of the original formula below are correct, those I asked for). =IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing",IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through", Remove them and see if that makes a difference. Rick IF(OR(AA7<"",OR(AND(TODAY()AG7,(AN7="AC Initiated")))),IF(AND(TODAY()AG7,(AN7="AC Initiated")),IF(AP7="","Check AC Status",IF(AND(TODAY()(EDATE(AP7,6)),OR(AN7="AC Initiated")),"Re-Check AC Status","")),IF(AND(TODAY()EDATE(AA7,12),OR(AN7=" Referred to TX")),IF(AL7="","Eval Before RF",IF(AND(TODAY()(EDATE(AL7,6)),OR(AN7="Referr ed to Tx")),"Confirm AC Follow Through","")),"")),IF(AL7<"",IF(AND(TODAY()EDATE (AL7,6),OR(AN7="Referred to Tx")),"Confirm AC Follow Through",""),"")))) "Rick Rothstein (MVP - VB)" wrote: Sorry, I apparently misread your 2nd "large" paragraph as indicating your existing formula worked as expected accept for the greater than one year problem. We should be able to handle the less than six months problem with another IF function (I think<g). Instead of what I suggested earlier, try putting this... IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing",IF(TODAY()EDATE(AL7,6),"Confirm AC Follow Through", in front of the **ORIGINAL** formula you had (that is, after the equal sign but before the first IF) and put TWO closing parentheses at the end of that original formula. Now, there is a chance that you will have some non-essential testing conditions left over inside that original formula (I'm not 100% sure) as a result of this, but it shouldn't matter... the only dates it will have to process are those where AL is less than 6 months (any other condition testing for a larger AL date, if in there, will not activate). Rick "Dan the Man" wrote in message ... Thanks Rick. That got me closer, but still not 100%. With your suggestion, the "Update Eval Before Hearing" flag does appear if the date in Cell AL7 is greater than 12 months, however if I enter a date under 6 months, the flag "Confirm AC follow Through" still shows up. That flag should only appear (per the formula below) if more than 6 months, but less than 12 months has elapsed. I appreciate your suggestion. Dan "Rick Rothstein (MVP - VB)" wrote: Without studying your formula in any detail, and based solely on your description of the addition you want and the fact that the existing formula works correctly, I would guess that putting this (don't miss the comma at the end)... IF(TODAY()EDATE(AL7,12),"Update Eval Before Hearing", in front of the formula you now have (that is, after the equal sign but before the first IF) and placing a closing parenthesis at the end of your existing formula, would do what you want. Rick "Dan the Man" wrote in message ... Hey Guyz! Since Max and Ron have been so helpful, I'll throw out a more complex formula. My best friend is a senior software engineer, however since he's got |
| All times are GMT +1. The time now is 01:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com