HOW TO MANAGE UPDATE/ADD DATA ACROSS SHEETS

Options

Hey everybody!


I am collecting answers from an oline survey on a Sheet called Survey.


After a news row is added, I need an automation to check if the same email address is already present on a separate Sheet called Lead.


If yes, then I need the values from Survey to update the corresponding columns in Lead.

If not, I need a new row to be added at the bottom of Lead.


How can I do that?

Answers

  • Jessica Chandler
    Options

    There is probably a better way to do this and someone out there likely has a far more elegant solution but in the event that is not the case, I would set it up (in theory) using a combo of VLOOKUPS and Automated Workflows.

    In your Survey sheet, create a workflow that is triggered whenever a row is added to change a cell.

    That cell would automatically become a formula to check to see if an email address is already present on a separate sheet. I would use a VLOOKUP type formula and return a value of some kind such as "Yes" into a new column if found and #NOMATCH if not.

    VLOOKUP( search_value, lookup_table, column_num, [ match_type ])

    https://help.smartsheet.com/function/vlookup

    If yes, then create a second version of this formula in reverse on the LEAD sheet for each column that you want to update.

    If no match then create a workflow that is triggered when that field changes to "#NOMATCH" that copies or moves a row to another sheet.