Cross Sheet Formula with One Criteria

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.

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @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.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

  • Jeff Reisman
    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.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • kelceyg
    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å
    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • kelceyg
    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
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @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.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Andrée Starå
    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!