Are you able to associate answers with number values?

Michael Richard

I am attempting to create a form similar to the one attached below:

Is there a way to associate the answers a user inputs on the form to a number value and then add them up to a total?

Thank you for the help in advance!

Genevieve P.

Hi @Michael Richard

Form submissions create new rows in a sheet, so to have this type of data come through a form, you'd actually need to swap around the rows/columns.

Your columns would be your questions, and one row would be one submission. Then you could have each column be a drop-down selection of these 7 options... for example, this is how I would set it up in a sheet:

Then if you had the number/value in the first place of the drop-down selection (like above, "1 - All the time"), you could use a SUM formula to take the VALUE of the very LEFT character of that selection and add together each cell's value.

For example, with my column names it would look something like this:

=SUM(VALUE(LEFT([Nervous?]@row, 1)), VALUE(LEFT([Hopeless?]@row, 1)), VALUE(LEFT([Restless?]@row, 1)), VALUE(LEFT([Depressed?]@row, 1)), VALUE(LEFT([Effort?]@row, 1)))

Here are some Help Center articles that I used to create the above: Forms in Smartsheet / Dropdown Lists / VALUE function / LEFT function / @row function

Does that make sense and do you think it will work for you?

Cheers!

Genevieve

Genevieve P.

Hi @Michael Richard

Form submissions create new rows in a sheet, so to have this type of data come through a form, you'd actually need to swap around the rows/columns.

Your columns would be your questions, and one row would be one submission. Then you could have each column be a drop-down selection of these 7 options... for example, this is how I would set it up in a sheet:

Then if you had the number/value in the first place of the drop-down selection (like above, "1 - All the time"), you could use a SUM formula to take the VALUE of the very LEFT character of that selection and add together each cell's value.

For example, with my column names it would look something like this:

=SUM(VALUE(LEFT([Nervous?]@row, 1)), VALUE(LEFT([Hopeless?]@row, 1)), VALUE(LEFT([Restless?]@row, 1)), VALUE(LEFT([Depressed?]@row, 1)), VALUE(LEFT([Effort?]@row, 1)))

Here are some Help Center articles that I used to create the above: Forms in Smartsheet / Dropdown Lists / VALUE function / LEFT function / @row function

Does that make sense and do you think it will work for you?

Cheers!

Genevieve

Michael Richard

Awesome! This does look like it will work. I am going to give it a try ASAP.

Thank you so much!

Genevieve P.

No problem!

If you encounter any issues or you're not sure what I did above, let me know. If you're able to post a screen capture of your sheet with any further questions that would help a lot. 🙂