Sunday, September 23, 2012

yogi_Compute Stats For A Dynamically Growing Table Both In Number Of Columns And Rows

Yogi Anand, D.Eng, P.E.      Google Spreadsheet   Post  #777  Sep 23, 2012

user Hermann the german said: (!category-topic/docs/spreadsheets/eVGdjbqkJZM)
What is the best way for an automated event query?

I want to query an e-mail list of about 50 members to regular weekly events. The events (occurred 1 or not 0) should be entered without my help in a table. There, there is some simple statistics with the values​​.
Each member should be allowed to enter only their own messages. The feedback could e.g. via the e-mail answer.

So far I have conducted in such a table by hand and entered the e-mail replies. Now I want to automate the whole thing.

Are there any ideas on this. Which Google tools should I use for this project? Calendar, Drive / docs scripts? A solution should not be too sophisticated. I would not invest more work into the project than I save.

Thank you for your suggestions and tips!

the following solution provides a degree of automation

I have converted the affected formulas to array formulas so that more data can be entered in more rows without having to drag down formulas

the only two formulas that have to be dragged to the right when one adds more names to the right are the formulas in cells

Further automation of this is possible in the sense that one can create a Google Form via which the persons with the pagers can submit their relevant information via the Form and the information will be automatically logged in a so called Form Responses sheet ... but that may or may not be of interest here.

For more automation, one should explore using Google Apps Script.