How to: Google Docs Form with Multiple Spreadsheets

I caught a question on Twitter today about Google Docs Forms and Spreadsheets:

 

I have been sharpening my skills in the art of Spreadsheet-itsu the past 3-4 years through little challenges like this from work or elsewhere.

So the answer to the question is yes you can:

  1. Create your Google Docs Form
  2. Open the workbook that backs your form and insert a new sheet ex. Sheet2
  3. Use formulas to populate the other worksheet, remember to copy the formula across the whole column.
Above we just pull the data from Sheet1 and populate Sheet2 as well. We can then extend this to build in some logic into the subsequent worksheets. For example I can use this formula to populate my second sheet only with the rows where “Female” was selected, and then have another worksheet for when “Male” was selected. Formulas like this can help me break down the data and look at some underlying patterns easily.

=IF(Sheet1!B2=”Female”,Sheet1!C2,”")

One issue I couldn’t get around was that every time a new record was submitted into Sheet1 from the form, that record was missing from the subsequent sheets. What happens is that every time the form gets submitted, Google Docs inserts a new row into your spreadsheet which ends up shifting your row index in subsequent sheets. Ex. below, what used to be my 9th row became started to reference data from row 10 in the form’s worksheet after the form was submitted –  so I’m missing data from row 9.

Sheet 2 Row 8
=ArrayFormula(IF(Sheet1!B8=”Female”,Sheet1!C8,”"))

Sheet 2 Row 9
=ArrayFormula(IF(Sheet1!B10=”Female”,Sheet1!C10,”"))

I could not figure out how to get around this – but there probably is a way to resolve this. I guess the temporary solution is to recopy the formula across the columns  before you start analyzing the data in the subsequent sheets.

This entry was posted in Google, how to. Bookmark the permalink.

2 Responses to How to: Google Docs Form with Multiple Spreadsheets

  1. David says:

    Hi Robert,
    Did you ever find a workaround for Google Forms automatically inserting a new row?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>