HOW TO MANAGE UPDATE/ADD DATA ACROSS SHEETS

AB consulting

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?

Jessica Chandler

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.