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    www.energyefficientbuild.com.

user Hermann the german said: (http://productforums.google.com/forum/?zx=efkljiwxbna#!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!

Hermann
---------------------------------------------------------------------------------------------------
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
G2
and
G4

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.


2 comments:

  1. Hi Yogi,

    yogi wrote:
    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

    this is really interesting stuff, exactly what I want to. Unfortunately I have no experience and do not know how I could do that :-(.
    Is there a tip on how I could start the best?

    Cheers
    Hermann

    ReplyDelete
  2. Hi Herman:

    You can set up a Google Form pretty easily ... you may want to start with a simple Form first, and then have a little play with it to see how the data from Form submissions is logged into the so called Form Responses sheet and how you can work with the data -- it is pretty simple but quite powerful ... so check it out to see how this works for you.

    Cheers!
    Yogi
    Cloud Computing -- Google Docs Way
    yogi--anand-consulting.blogspot.com

    ReplyDelete