How to: Google Docs Form with Multiple Spreadsheets

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

Can one #Google form populate multiple spreadsheets? If so, how? #help #GoogleDocs

— Roberto Sarjoo (@robsarj) December 19, 2011

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.
   

=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.