title: Google Sheet Array Formula to automatically transpose responses from a Google Form into a single cell
body: <p>I want to tweak an Array Formula that I'm trying to use to transpose a set of responses from a Google Form into a single 'list' in a cell in the linked Google Sheet. </p>
  
  <p>Here is a simplified mock-up of the Google Sheet: <a href="https://docs.google.com/spreadsheets/d/1BKgjGK2RbXC5FkCgBOU53dLEb3fDeQADujenrqRgnT0/edit?usp=sharing" rel="nofollow noreferrer">https://docs.google.com/spreadsheets/d/1BKgjGK2RbXC5FkCgBOU53dLEb3fDeQADujenrqRgnT0/edit?usp=sharing</a></p>
  
  <p>As you can see, I have a working Array Formula 'hidden' in the header for Column A, which takes the content from columns B:K in each row and transposes them with line breaks to make a nice neat list in the first cell at the start of each row (A2, A3, A4, etc.) Here is the Array Formula for ease of reference: </p>
  
  <pre><code>=ARRAYFORMULA({"Points to Develop";
  transpose(query(transpose ($B$2:$B&amp;CHAR(10)&amp;$C$2:$C&amp;CHAR(10)&amp;$D$2:$D&amp;CHAR(10)&amp;$E$2:$E&amp;CHAR(10)&amp;$F$2:$F&amp;CHAR(10)&amp;$G$2:$G&amp;CHAR(10)&amp;$H$2:$H&amp;CHAR(10)&amp;$I$2:$I&amp;CHAR(10)&amp;$J$2:$J&amp;CHAR(10)&amp;$K$2:$K)
  ))})
  </code></pre>
  
  <p>The problem I'm having is that the formula stops working as soon as a new Google Form is submitted, as the new row of responses arrives at the top and 'pushes the formula down'/throws everything out of alignment, when all I want is for it to stay in place and run automatically every time a Form is submitted. As you can probably gauge by my limited technical terminology, I'm very much a novice at this!</p>
  
  <p>How can I fix this formula, or is there a simpler or better alternative that will achieve the same results?</p>