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

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!

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!