HOW TO MANAGE UPDATE/ADD DATA ACROSS SHEETS
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
-
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.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 464 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives