Auto update target sheet status from source sheet

jeannaplummer

I have an Issue Log sheet (source) that if a row status is equal to either "Assigned" or "In Progress" then I want the Testing sheet (target) to update it's matching (match is by ScenarioStepID) row status to "Failed-Issue Reported." I also need the same basic updating, but if the source row is "Retest" then the target needs to update to "Ready for Retest." Otherwise, I want the target row status to remain blank for manual update.

I would also really like for when there is a row on the source sheet that was set to "Retest," that it automatically updates to "Closed/Resolved" if the matching row on the target sheet changes from "Ready for Retest" to "Completed" OR if the target row changes back to "Failed-Issue Reported" then the source row status should update from "Ready for Retest" to "Assigned."

Note: Statuses are drop-downs in both sheets

I tried putting together an initial attempt at a formula, but it is erroring on me (and I'm still pretty new to this COUNTIFS formula): =IF(COUNTIFS({WIP-Butterfly UAT Issue Log ID}, {WIP-Butterfly UAT Issue Log NAMEDID} = ScenarioStepID@row) AND(OR({WIP-Butterfly UAT Issue Log Status} = "Assigned", {WIP-Butterfly UAT Issue Log Status} = "In Progress"), "Failed-Issue Reported"))

Screenshot of Target Sheet:

Screenshot of Source Sheet:

jmyzk_cloudsmart_jp

Hi @jeannaplummer

Since your conditions are rather complex, I summarized them as follows.

https://app.smartsheet.com/b/publish?EQBCT=4b240eec5e9e421999ef3ea96657f9a6

Additional Columns

I added Manual Input columns in both the source and target sheets. Your conditions or logic are bi-directional. So, if you automate the target status with the source status and the source status with the target status, the formulas automating the change get into loop status, resulting in errors.

CIRCULAR REFERENCE

https://help.smartsheet.com/articles/2476176-formula-error-messages#toc--circular-reference )

So, we add two Manual Input columns to avoid this error.

To make formulas manageable, we add two helper columns referencing the status on the other sheet.

Target Status: =JOIN(COLLECT({Target: Status}, {Target: ID}, ScenarioStepID@row))

Source Status: =JOIN(COLLECT({Source: Status}, {Source: ID}, ScenarioStepID@row))

Source Status formula

=IF(AND([Manual Update]@row = "Ready for Retest", [Target Status]@row = "Completed"), "Closed/Resolved", IF(AND([Manual Update]@row = "Ready for Retest", [Target Status]@row = "Failed-Issue Reported"), "Assigned", [Manual Update]@row))

The first IF clause is for the No.5 condition, and the second is for the No.6 condition.

The first IF checks if the Manual Update column value is "Ready for Retest." Then, a formula at the target for condition No.3 sets the Target Status to "Ready for Retest." But, the formula sets the Target Status to "Completed" if the Manual Input changes to "Completed". Then, the helper Target Status column at the Souce changes to "Completed." Finally, "AND([Manual Update]@row = "Ready for Retest", [Target Status]@row = "Completed")" becomes true, so the formula sets the Source Status to " "Closed/Resolved."

The same structure applies to the second IF clause to set the value to "Failed-Issue Reported."

Otherwise, the formula gets the [Manual Update]@row value.

https://app.smartsheet.com/b/publish?EQBCT=614081940ca949169e7e49b64d05d209

Target Status formula

=IF(OR([Manual Update]@row = "Completed", [Manual Update]@row = "Failed-Issue Reported"), [Manual Update]@row, IF(OR([Source Status]@row = "Assigned", [Source Status]@row = "In Progress"), "Failed-Issue Reported", IF([Source Status]@row = "Ready for Retest", "Ready for Retest", [Manual Update]@row)))

or for formula readability;

=

IF(OR([Manual Update]@row = "Completed", [Manual Update]@row = "Failed-Issue Reported"), [Manual Update]@row,

IF(OR([Source Status]@row = "Assigned", [Source Status]@row = "In Progress"), "Failed-Issue Reported", IF([Source Status]@row = "Ready for Retest", "Ready for Retest",

[Manual Update]@row)))

For simplicity, I explain from the 2nd IF clause. The If clause corresponds to the 1st and 2nd conditions. < Source: (Assigned or In Progress) = Target: Failed-Issue Reported >

The 3rd IF clause corresponds to the 3rd condition. <Source: Ready for Retest = Target: Ready for Retest>

The 1st IF clause is tricky. The If clause overrides the 2nd and 3rd if to get the Manual Update]@row if the values are either "Completed" or "Failed-Issue Reported." In other words, normally, the Target Status receives the value from the source sheet with the 2nd and 3rd IF clauses, but if the target sheet's Manual Update is either "Completed" or "Failed-Issue Reported," those values prevail. Then, the formula in the source sheet looking at the Target Status changes the source status to "Closed/Resolved."

Otherwise, the formula gets the Manual Update row's value, meeting the No3 condition.

https://app.smartsheet.com/b/publish?EQBCT=be7cd10b347c4bf8a891f14309aaa763

To view everything in one place, please look at the demo dashboard.

https://app.smartsheet.com/b/publish?EQBCT=b573555e1108457bbc27807888f9557e

I checked that the demo solution meets all the five conditions you specified if I changed the status in the update condition sheet's order. The conditions order aligns with the logical course of action for issue reporting, assignment, update status to In Progress, and then to Ready to Retest. Then, on the target sheet side, the Ready to Retest gets changed to "Completed" if the test is successful and "Failed-Issues Reported" if not.

But, there may be another course of action that this solution needs to be revised. If so, please do not hesitate for further assintance.

jeannaplummer

@jmyzk_cloudsmart_jp this is AMAZING. Thank you so much for your work on this - I knew it wasn't a straight-forward ask, but wow! I will be working through this with your provided samples over the next few days. Really appreciate all of the insight here to help me really know what is going on. Thank you!

jmyzk_cloudsmart_jp

@jeannaplummer

If you need any more assistance while you're exploring the samples, feel free to reach out. Happy to help!😁