Cross Sheet Formula with One Criteria

kelceyg

Hello,

I am creating Smartsheets for quality assurance testing. Sheet #1 will be the test scenarios and steps taken. The row will include a drop down menu to indicate if the step passed, failed, etc.

I need a formula that if "Fail" is selected from the drop down menu, the step's ID # will automatically pull into Sheet #2

Sheet #1:

Sheet #2

If, for example, step 1.01 from Sheet #1 is marked as "Fail", then 1.01 would appear in the Smartsheet ID column in Sheet #2.

I am assuming it needs to be a VLOOKUP formula, but I cannot seem to work it out.

Jeff Reisman

@kelceyg

Some things that might hinder a row copy/move:

A column in the destination containing a column formula with the same name as a column in the source - the formula cannot be overwritten so the copy/move will fail.

When the source contains columns that don't exist in the destination, and you copy/move a row, the missing columns will be created in the destination sheet. If you don't have Admin or Owner rights on the destination sheet, the copy/move will fail because you don't have rights to create those columns.

If you're only an editor on the destination sheet, and try to copy/move data into a locked column in the destination sheet, the row copy/move will fail.

Another option would be DataMesh, a premium add-on that many Enterprise-level accounts might have already. It lets you automate data copies into other sheets when rows are added or changed, where you can match differently-named columns together and only copy data from selected columns in the source to selected columns in the destination.

Jeff Reisman

How about using Copy row automation?

Trigger: When a row changes,

when Status changes to 'Fail'

Action:

Copy row to Sheet #2.

If you make your Sheet #2 Row ID column into an auto-number system field, you can automatically assign a unique defect number.

kelceyg

I love the simplicity of that option! Question - to do this, do the columns of the two sheets need to match?

Andrée Starå

Hi @kelceyg

I hope you're well and safe!

No, the columns would be created automatically when you copy over your first row, and then you can add additional columns as needed.

Another option is if you only want to include specific columns. You can take a look at my workaround below.

You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

Another option would be to use so-called helper sheets. In short, copy the row to a helper sheet and then use my method described previously to get the values you need to another helper sheet, and then copy/move the row from that sheet to the main destination sheet.

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

kelceyg

Hello!

I tried to set up the copy row automation and ran a test, but the row never showed up in my Sheet #2.

Automation in Sheet #1:

Test entered in Sheet #1:

Sheet #2 after saving test scenario one, refreshed, and over an hour later:

Jeff Reisman

@kelceyg

Some things that might hinder a row copy/move:

A column in the destination containing a column formula with the same name as a column in the source - the formula cannot be overwritten so the copy/move will fail.

When the source contains columns that don't exist in the destination, and you copy/move a row, the missing columns will be created in the destination sheet. If you don't have Admin or Owner rights on the destination sheet, the copy/move will fail because you don't have rights to create those columns.

If you're only an editor on the destination sheet, and try to copy/move data into a locked column in the destination sheet, the row copy/move will fail.

Another option would be DataMesh, a premium add-on that many Enterprise-level accounts might have already. It lets you automate data copies into other sheets when rows are added or changed, where you can match differently-named columns together and only copy data from selected columns in the source to selected columns in the destination.

Andrée Starå

Hi @kelceyg

I hope you're well and safe!

To add to Jeff's excellent advice/answer.

Also, look at the bottom of the sheet to ensure it wasn't added there. If it's not, you could also check the Activity Log to see if the Workflow is triggered.

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!