Automate manual data entry

KristinaMiteva
edited 12/22/21 in Smartsheet Basics

Hi, how do I automate this:


I need the sheet 1 to search and compare the contents of a column filled with work order numbers and letters with sheet 2 and if there is no match (the work order is missing), then take the missing work order from sheet 2 and add it to the bottom of sheet 1. Thanks

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @KristinaMiteva

    You can use a COUNTIF formula to check a box if there is a count of 1 (or more) of your criteria across sheets. For example, put this in Sheet 2:

    =IF(COUNTF({Work Order Sheet 1}, [Work Order Number]@row) > 0, 1, 0)

    This looks through your Work Order Number column in Sheet 1 and checks to see if the current Work Order Number in this row (of Sheet 2) appears in that sheet. If it does, all is well and it checks the box. If there is no instance, it will return 0, or an unchecked box.

    Then once you have this indicator on each row, you can set up a Copy Row workflow that checks the sheet once a day to copy over rows that have an unchecked box, like so:


    Cheers,

    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @KristinaMiteva

    You can use a COUNTIF formula to check a box if there is a count of 1 (or more) of your criteria across sheets. For example, put this in Sheet 2:

    =IF(COUNTF({Work Order Sheet 1}, [Work Order Number]@row) > 0, 1, 0)

    This looks through your Work Order Number column in Sheet 1 and checks to see if the current Work Order Number in this row (of Sheet 2) appears in that sheet. If it does, all is well and it checks the box. If there is no instance, it will return 0, or an unchecked box.

    Then once you have this indicator on each row, you can set up a Copy Row workflow that checks the sheet once a day to copy over rows that have an unchecked box, like so:


    Cheers,

    Genevieve