How do you link certain information from a form to a sheet or report?

Laszlo B

I am trying to link certain information from a form to another sheet. The form is a questionnaire with a Likert scale from 1-5. If the user select a 1 or 2, they are required to put in a comment. The comment is another question which shows as a column on the sheet. And lastly, the user who is filling out the form will select which customer this evaluation belongs to.

I am trying to get the 1 and 2 scores onto a new sheet with the corresponding comment. Ideally, I would have this information along with the customer and the title of the question that the score and comment belongs to. Each customer would be on a different sheet or report which wouldn't be difficult because I can just filter by that customer. There would be up to 150 questions with corresponding comments so it could be up to 300 questions on the form in total if that bit of information helps.

How the information shows from the Form (please ignore all of the colors and what not):

This is ideally how the information would show on a new sheet or report:

I tried to put together an IF statement which in my mind would go like: IF the customer from this sheet is "Customer A", and the score of the questions is less than 3, and there is a comment, then pull that information forward.

Hopefully this hasn't confused all of you and someone is able to help me. Below is my stab at putting this into a function but it isn't working.

=IF({Test Form 1 Range 1} = "Customer A", AND({Test Form 1 Range 2} <= 2, AND(NOT(ISBLANK({Test Form 1 Range 3})))) = {Test Form 1 Range 3})

Any advice is greatly appreciated, thank you!

Genevieve P.

Hi @Laszlo B

You could potentially use a JOIN(COLLECT to bring together all comments from that one customer into one cell, if the row is associated with a 1 or two. This formula would be structured like this:

=JOIN(COLLECT({Comment Column}, {Customer Column}, "Customer A", {Question 1 Column}, OR(@cell = "1. Failing", @cell = "2. Not Acceptable"), " / ")

This presumes that the customer may have submitted more than one row, and there could be more than one row in Question 1 that has "1" or "2". Then in this instance two comments would show up. Does that make sense?

Let me know if this works for you!

Cheers,

Genevieve

Josh L.

Hi - I built something similar a while back. The way I did it was to create a Report as the landing place for the data. I linked the data from the Report to the Sheet that I wanted to use. So in your case you may have to look at it this way: Form --> Report --> Sheet.

The screen shot below is the right click menu on a Report.

Laszlo B

https://community.smartsheet.com/discussion/comment/303121#Comment_303121

I could link each cell but that would be a lot of manual and redundant work, I am trying to find a way to automate this as much as possible

Genevieve P.

Hi @Laszlo B

You could potentially use a JOIN(COLLECT to bring together all comments from that one customer into one cell, if the row is associated with a 1 or two. This formula would be structured like this:

=JOIN(COLLECT({Comment Column}, {Customer Column}, "Customer A", {Question 1 Column}, OR(@cell = "1. Failing", @cell = "2. Not Acceptable"), " / ")

This presumes that the customer may have submitted more than one row, and there could be more than one row in Question 1 that has "1" or "2". Then in this instance two comments would show up. Does that make sense?

Let me know if this works for you!

Cheers,

Genevieve

Laszlo B

I worked with the Pro Desk person and got the equation to work just as you have it @Genevieve P. , thank you for your help and insight!

Genevieve P.

No problem! I'm glad you were able to get it working.