pdeshazier
03-06-2007, 07:36 AM
I have a spreadsheet which contains 3 columns, such as:
COL C COL D COL E
ACI MEDICARE PART B UHCC
ACI TENNCARE MEDICARE
ACI MCR UHCC MEDICARE
ACI MCR MEDICARE
The "rule" is that col C should contain "ACI MCR" if EITHER columns D or E contain the word MEDICARE. If column C = "ACI MCR" and neither column D nor E contain 'MEDICARE', I need the word ERROR returned for the user. Likewise, if column D or column E do contain 'MEDICARE' and column C does not contain "ACI MCR", i need the work ERROR returned. This latter scenario is the most probable. I've tried nested if statements, using MID, AND, OR to try to achive the desired result, but can't get it to work correctly on every scenario. Here's what I currently have as a formula in 2 different columns, but need to combine them so the user only has to look for "ERROR" in one column:
IF(MID(D1,1,8)="MEDICARE",IF(C1="ACI MCR","","ERROR"))
IF(MID(E1,1,8)="MEDICARE",IF(C1="ACI MCR","","ERROR"))
Thanks in advance for any help.
COL C COL D COL E
ACI MEDICARE PART B UHCC
ACI TENNCARE MEDICARE
ACI MCR UHCC MEDICARE
ACI MCR MEDICARE
The "rule" is that col C should contain "ACI MCR" if EITHER columns D or E contain the word MEDICARE. If column C = "ACI MCR" and neither column D nor E contain 'MEDICARE', I need the word ERROR returned for the user. Likewise, if column D or column E do contain 'MEDICARE' and column C does not contain "ACI MCR", i need the work ERROR returned. This latter scenario is the most probable. I've tried nested if statements, using MID, AND, OR to try to achive the desired result, but can't get it to work correctly on every scenario. Here's what I currently have as a formula in 2 different columns, but need to combine them so the user only has to look for "ERROR" in one column:
IF(MID(D1,1,8)="MEDICARE",IF(C1="ACI MCR","","ERROR"))
IF(MID(E1,1,8)="MEDICARE",IF(C1="ACI MCR","","ERROR"))
Thanks in advance for any help.