Trying to find a way to update information on existing rows via a form (not via update request)
Hello Smartsheet Community. I have a customer who has uploaded an existing excel spreadsheet into Smartsheet (about 200 rows) which includes information such as email address, first name, last name, etc. as columns. We have created a form to capture new information to each row. Is there a way to set it up so that the new information goes to their existing line? And update request does not work because the form has logics and drop downs which I cannot mimic in an update request.
Thanks for any help :)
Best Answer
-
You would have to have the form going to a separate sheet and then use INDEX/MATCH formulas with cross sheet references to pull the values over to the original sheet. If you wanted to "lock" that data in as static data, you would have to set up a trigger that denotes the row is completed being updated and use a copy or move row automation to a 3rd sheet.
Answers
-
You would have to have the form going to a separate sheet and then use INDEX/MATCH formulas with cross sheet references to pull the values over to the original sheet. If you wanted to "lock" that data in as static data, you would have to set up a trigger that denotes the row is completed being updated and use a copy or move row automation to a 3rd sheet.
-
@Paul Newcome Thanks so much for this response. Do you know if there is a limit to how many cells I can do this for?
-
There are going to be quite a few variables that come into play for this. Aside from the regular sheet limitations you also need to keep in mind that you can only have up to 100 unique cross sheet references and can only reference 25,000,000 cells in total for the sheet.
I see you mentioned about 200 rows. How many columns are there in total? How many columns would you be updating using this method? Would it be a single form entry for each row, or could you have multiple form entries to update each row? Is it even possible that a row would be "finished" updating or would you need to maintain the ability to update all 200 rows for the foreseeable future?
-
@Paul Newcome I am not sure yet on how many columns it will be or how many rows will need to be updated via this method. Yes, it will be a single form entry for each row. Yes, once the row is updated we will no longer need this option anymore. It is just going to be used for the existing entries that we are uploading from Excel. Any new entries will come straight from the form. Does that make sense? I have a meeting tomorrow with this client and will ask about how many rows and columns and then see if I can do what you suggested. Thank you for your quick responses and help :)
-
In that case I would suggest that the form for NEW entries be attached to the sheet that you are copying to.
-
Yep. That is exactly how I am going to do it. Thank you again for your input.
-
Happy to help. 👍️
