Formula to Return Value Based on Multiple Criteria "If-And"

CLH707

I'm trying to write a formula to return a value based on multiple criteria. I've gotten other similar to work, but this one is not returning the correct value for some reason (or not clearing the value to a blank ("") when it should).

The value I'm trying to have the formula return is "Open Go-Back - Corrections Submitted" based on set criteria. The criteria is contained between 4 different sets of 2 columns ("Go-Back#X QA Pass - Yes or No, and Go-Back #X Resubmittal Date, in which the Go-Back#X columns are labeled 1 through 4 for each of those items).

The formula I'm attempting to write should return:

True Value = "Open Go-Back - Corrections Submitted"

If "Go-Back #3 QA Pass" column value = "No" and there IS A Date in "Go-Back #4 Resubmittal Date" column = True

If "Go-Back #3 QA Pass" column value does not = "No" (Equals Yes or Blank), then false

If False then it should look at the next set of logic which would be:

If "Go-Back #2 QA Pass" column value = "No" and there IS A Date in "Go-Back #3 Resubmittal Date" column = True

If "Go-Back #2 QA Pass" column value does not = "No" (Equals Yes or Blank), then false

This would continue for 2 more steps and then if the last is value turns up false then the value would be blank ("").

I've tried a few different formulas without getting a correct value returned, but have gotten close.

The formula returns a correct value, but does not return a blank value correctly. i.e. if Go-Back #1 QA Pass = "No" and there is a date in the "Go-Back #2 Resubmittal Date column", and then Go-Back #3 QA Pass = "No" and there is no date in the "Go-Back #4 Resubmittal Date" it should return a false value of "", but I believe it is still picking up that the downstream Go-Back #1 QA Pass="No" and Go-Back #2 Resubmittal Date is a true value and still returning that true value.

=IF(AND([Go-Back #3 QA Pass (Yes/No)]@row = "No", (ISDATE([Go-Back #4 Resubmittal Date]@row))), "Open Go-Back - Submitted Corrections", IF(AND([Go-Back #2 QA Pass (Yes/No)]@row = "No", ISDATE([Go-Back #3 Resubmittal Date]@row)), "Open Go-Back - Submitted Corrections", IF(AND([Go-Back #1 QA Pass? (Yes/No)]@row = "No", ISDATE([Go-Back #2 Resubmittal Date]@row)), "Open Go-Back - Submitted Corrections", IF(AND([1st Submittal QA Pass? (Yes/No)]@row = "No", ISDATE([Go-Back #1 Resubmittal Date]@row)), "Open Go-Back - Submitted Corrections", ""))))

Genevieve P.

Hi @CLH707

Try adding in a Condition in each of your AND statements that looks one ahead to see if that cell is blank or not.

If the next phase is blank, you can keep your current formula. However if it ISN'T blank (meaning that there's a Yes or No in the next numbered column) we want it to check that pairing instead of the current one, so it should move on to the next statement.

I've bolded the 3 additions below:

=IF(AND([Go-Back #3 QA Pass (Yes/No)]@row = "No", ISDATE([Go-Back #4 Resubmittal Date]@row)), "Open Go-Back - Submitted Corrections", IF(AND([Go-Back #2 QA Pass (Yes/No)]@row = "No", ISDATE([Go-Back #3 Resubmittal Date]@row), [Go-Back #3 QA Pass (Yes/No)]@row = ""), "Open Go-Back - Submitted Corrections", IF(AND([Go-Back #1 QA Pass? (Yes/No)]@row = "No", ISDATE([Go-Back #2 Resubmittal Date]@row), [Go-Back #2 QA Pass (Yes/No)]@row = ""), "Open Go-Back - Submitted Corrections", IF(AND([1st Submittal QA Pass? (Yes/No)]@row = "No", ISDATE([Go-Back #1 Resubmittal Date]@row), [Go-Back #1 QA Pass? (Yes/No)]@row = ""), "Open Go-Back - Submitted Corrections", ""))))

Let me know if that makes sense and works for you!

Cheers,

Genevieve