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", ""))))
