How to update rows from a custom form...

Bladerunner

Hi, I have a Smartsheet that contains information about different pieces of work. Each row is specific to a piece of work and it has multiple columns, one of these is Status. I display this as a Kanban using Card view.

When I change the status of an item to Completed, I want to automatically send a form to a contact in one of the cells. The form is created as a feedback form with fourteen questions and uses a simple 0-5 star rating drop down box. The questions relate to 14 additional columns in the same sheet which are unpopulated.

How can I get the cell status changing to completed to automatically send the form, and that form to update the unpopulated columns on the correct line?

I have also tried using a standard Request Update, and although that does allow me to send just the 14 new columns for the update, the column Name field is not large enough to take a full question. I have tried putting the question into Column Description, but that doesn't appear on the Request Update Form.

I have also tried to create a form and trigger that through an Action when the cell value changes, but I can't seem to be able to send a form in that way.

I would also add that it is not essential that the answers populate the same sheet, it could be another sheet, as long as I can take the Description field from the original sheet to cross-reference with, so yes I could send a link to a new form, but how would I get a reference to the row into that new form.

It's very frustrating, it seems to be almost possible...

Apologies if this appears more than once, the Community site went down while I was originally posting the question.

 

 

Thanks in anticipation. Ben. 

 

Andrée Starå

Hi Ben,

I've answered your other post already, and now I see more details here. I'll get back to the post a little later.

You'd use an update request thru automation triggered by the status changing to Completed.

Hope that helps and let me know if you need any help!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Andrée Starå

I have a few ideas on how to solve this! The easiest would probably be to add columns for the description and the text to the cells on each row.

Make sense?

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Have a fantastic week!

Best,

Andrée

Bladerunner

Hi Andree,

 

I've found a way to do it by putting the question in one column and then making the next column the answer column. The question column is locked and the answer column is set to be symbols and uses the 0-5 stars symbol set. I can add the explanation in as a custom message during the automation workflow which triggers a row update request when the status moves to complete. It's not as elegant as a custom form but it works.

 

My next challenge is to average the number of stars in each answer column so that I can create a KPI dashboard. However I am struggling with the format of the formula to convert the symbols to numbers and then average. I have tried a number of things substituting "one", "two", "three" etc for the number of stars but I can't get it to return anything, it's either always "0" or "unparsable"

Do you have any idea how to average a column of symbols?

 

Kind regards.. Ben.

Andrée Starå

Excellent!

Here's a solution to average using helper columns for each question. (you can hide what you don't wan't need to see)

Add a new column: Number Score for each question and use something like the below formula to convert the Stars to a number.

=IF(Stars@row = "Empty", 0, IF(Stars@row = "One", 1, IF(Stars@row = "Two", 2, IF(Stars@row = "Three", 3, IF(Stars@row = "Four", 4, IF(Stars@row = "Five", 5, 0))))))

Then you'll add something like the below formula to get average rounded to a whole number:

=ROUND(AVG(SELECT RANGE()))

If you wan't to show the average above as stars as well you'll use something like the below formula:

=IF([Number Score]@row = 0, "Empty", IF([Number Score]@row = 1, "One", IF([Number Score]@row = 2, "Two", IF([Number Score]@row = 3, "Three", IF([Number Score]@row = 4, "Four", IF([Number Score]@row = 5, "Five"))))))

Did it work?

Best,

Andrée