Friday, September 9, 2011

yogi_Extract and Collate Other Data From A Form Based Spreadsheet

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  
Cammo said:
How can I extract and collate "other" data from a form based spreadsheet?
I've tried searching for an answer to this, but have thus far failed to find anything relevant. I'm quite surprised, because I can't be the only person who's wanted to do something like this...
I have a form based spreadsheet that has radio button questions with answers like: Yes, No, Indifferent, Other. It also has check box type questions with answers like: Apple, Banana, Mango, Other.
In the above scenarios, somebody could click the "Other" radio button and enter "Depends on my mood". Equally, with the check boxes they might tick Apple, Mango and Other, and then enter "Tomato" for the "Other" entry.
What I want to do is extract these "Other" responses and collate them under a corresponding question heading.
An example of results might be something like:
Other responses:
Do you like to go out?
Depends on my mood.
Only on Fridays.
What's your favourite fruit?
With the radio button results, I could look for something that's not /Yes|No|Indifferent/ and take action based on that, such as copy to a list on another sheet.
It seems trickier with the check boxes, as I'd only want to copy out content that isn't /Apple|Banana|Mango/ for example. I'm not sure how I can go about this.
Also, the methods above seem very labour intensive in as far as I'd have to copy all the non "Other" text into formulas by hand to get it to work. Is there any way to reference the form text directly from the spreadsheet?
Thanks in advance for any help.