If you have ever used a Google Form where the data goes into a spreadsheet you may have noticed the tab where the Form data goes acts a little funny. For the most part I like to leave the “Form Responses” tab alone and use other tabs to write my formulas and organize the responses.
Method 1: Duplicate the sheet. Click on the little arrow on the tab and choose duplicate. This allows you to change the data, move columns, write formulas, delete data you didn’t want, etc… without messing up the original data.
This is my method of choice if I am done collecting the data, I find I frequently want to go back and look at the original data set, but like being able to totally mess up the spreadsheet. If I do mess it up, I can always make a new copy of the data and start over.
Method 2: Link to the “Form Responses” information on another tab. If you create another tab and put =’Form Responses’!A1 into the new sheet, what is in cell A1 from the form responses tab will show up.
You can then insert columns, delete columns or rows, or can just change the data without destroying your original values. What you type on the new sheet does not change the values in the “Form Responses” tab so you can play with the data without risking messing it up.
Method 3: The problem with using =’Form Responses’!A1 is that you can not sort that data since it is mirroring what is on the Form Responses sheet. The other problem is if a new response is submitted into the form it does not go to the next row on the Form Responses sheet it inserts a new row. This is an important distinction. If the form simply put the next persons responses on the next line, let’s say row 10, then on the new sheet you created =’Form Responses’!A10 would show the timestamp for that form submission. However, since it INSERTS a row, what WAS row 10 is now row 11. Spreadsheets automatically adjust your formulas when you insert rows or columns so your formula =’Form Responses’!A10 is automatically changed to =’Form Responses’!A11. Meaning that 10th row on Form Responses will not show up on your other sheet ever unless you redo the formulas.
This is the same reason why when you write a formula on the Form Responses sheet in a blank column that when a new response is submitted the formula does not appear next to that response.
Solution: Use =importrange(“spreadsheet key”,”tab range”)
While I usually use =importrange to bring one Google Spreadsheet data into another spreadsheet, you can actually use this within the same spreadsheet.
Create a new tab. In cell A1 write the formula =importrange(”
The URL at the top of the spreadsheet contains the spreadsheet key. It is a long string of weird numbers and letters. It is NOT the whole URL. Make sure you put this in quotations.
In quotations put the name of the tab, an exclamation mark and the range of cells.
Even though I may only have 20 responses in my Form Responses I will still write my import range to be from 1 to 300 so that as new responses come in I do not have to modify the formula.
Using the import range method when new responses are submitted, they will be referenced on the new tab and the formulas you write will still work.