If you have ever attended a meeting, workshop or a training and held your tongue because you were afraid to ask that one "silly question," only to find out that there were 10 people in the room that all had the same concern, then the following solution is for you.

This is a way to get an entire staff involved in the building of the agenda - after the meeting has already begun.

The long story short is this:

1) Attendees take Part 1 of a survey via a Google Form, this can happen before the meeting or right at the beginning of the meeting. In this Part 1 of the survey you will find out who is in the room and what questions/concerns they have about the content of the meeting.

2) When each attendee hits submit, their questions populate Part 2 of the survey. At some appropriate time, after everyone has submitted the first survey, launch Part 2: This one will ask everyone to pick the three most relevant or important questions/concerns from a list made up of questions from the first survey. Also included in Part 2 is the opportunity for attendees to select questions, posed by their peers, that they can help answer.

Once everyone has submitted this 2nd form, thanks to a built in trigger, Google will cast out an agenda doc which includes:

A) The 3 questions agreed upon as the most important from the people in the room.

B) A list of people who have offered to help others answer their questions.

C) A list of all questions posed so that you can attempt to answer them throughout the workshop.

Everyone in the room is amazed by your ability to so quickly pull them together onto one page, you have a segment of your meeting that is custom tailored to the needs of your team, and everyone is ready to lean forward and learn together. Below are the steps to creating this Dynamic Agenda:

Step 1

1. Merger Help

This area will Merge the person’s name with the Q’s they can answer

=if(H2<>"",B2&CHAR(10)&SUBSTITUTE(H2, ",", char(10)),"")

1. All Questions

This area will transpose all three Q’s from the row into the column. This is needed for FormRanger.

=UNIQUE(TRANSPOSE(SPLIT(JOIN(";",D2:D90,E2:E90,F2:F90),";")))

1. email

This will place a comma after each participant email so they can be merged into one cell later for one single email that goes to all participants with the agenda attached.

=if(I2<>"",I2&", ","")

1. Count

This will count how many times the question has been ranked as important.

=if(K2<>"",countif(G$2,"*"&K2&"*"),"")

Step 2

1. Build second tab: Sort

1. Column A: "All Questions" & Column B: "Rank"

Sort all questions from form response sheet - Sort by the rankings. Place this formula under the header in column A. This formula will keep a question with its number of times it was selected and rank the most often selected will sort to the top of the column.

=sort('Form Responses'!K2:L98, 2, FALSE)

1. Populate column C: "Help Offered"

This formula will pull the merged names with the questions they agreed they could answer.

='Form Responses'!J:J

1. Column D "All Questions"

This formula will hold all questions in one cell. The formula will pull them from the FormResponse tab, paste them all into one cell, and add a “Carriage Return” after each question. This will make it possible to mail merge to your agenda.

=trim(arrayformula(if(A2:A100<>"",concatenate(A2:A100&CHAR(10)),"")))

1. Column E "Help Merged"

This formula will merge all the people who offered help, and the q’s they said they can answer into one cell. This formula will also place a carriage return after each entry so the list comes out neat when you mail merge to your agenda.

=Trim(arrayformula(concatenate(C2:C100&CHAR(10))))

1. Columns F "#1 Question," G - "#2 Question," & H - "#3 Question"

These are the three questions that received the most selections. The formula for each simply selects the first, second and third cell in the “All Ranked Questions” column. Since this column (Column A) sorted by ranking, the highest ranked will be on top:

Column F "Question #1"

=A2

Column G "Question #2"

=A3

Column F "Question #1

=A4

1. Column I

This formula will merge all emails (with a comma after each) into one cell. This cell will be the To: line when you email out your agenda. All participant emails will be clustered on one line each separated by a comma - the way gmail likes it!

=CONCATENATE('Form Responses'!M2:M97,"")

1. Column J

OK, this is my favorite feature - This formula counts how many times the word “CloseNow” was entered in the “Name” column of the form.

Since nobody is ever named "CloseNow" this cell will read Zero until you open the form and enter “CloseNow” as your name. This will change the status of this cell to 1.

In autocrat you will set the merge trigger: When Column J = 1 - merge and send. This is your secret trigger that will send the merge to everyone.

=countif('Form Responses'!B:B,"CloseNow")

Step 4

Configure FormMule, FormRanger and AutoCrat

1. FormMule
   1. Install the script
   2. run installation
   3. Define Merge Sources - select FormResponse sheet - this is the tab you will run FormMule on. The next step is the key.

iv.FormMule>Advanced Options>Copy Down Formulas on Submit

v. Select the 4 columns with formulas in them and check the boxes for paste as value.

This will take the formulas we put in the columns to the right of the responses and copy them down to the next row every time there is a form submission.

1. FormRanger
   1. Install the script
   2. run installation
   3. assign items to columns

1. Q8: Please select the three most important questions.
   1. Form Responses - All Questions
2. Q9: Which of the questions below can you answer?
   1. Form Responses - All Questions

iv. Set Refresh Trigger

a. Upon Submission

1. AutoCrat
   1. Install the script
   2. run installation
   3. Choose a template Doc
      1. Select the Agenda that you created with the merge tags.
      2. Autocrat will recognize the merge tags>Save Settings
   4. Select the merge source
2. The merge source is the "Sort Tab"
3. Set Merge Conditions
   1. This will hold off the mail merge until all your participants have had a chance to rank the questions. Set the condition to Merge Status = 1.
   2. Remember it won’t equal 1 until you submit the form using “CloseNow” in the name entry field.

2. Set Field Mappings

1. For <<Question 1>> use Question 1 from Sort tab
2. For <<Question 2>> use Question 2 from Sort tab
3. For <<Question 3>> use Question 3 from Sort tab
4. For <<Merged Help List>> use Merged Help
5. For <<All Questions>> use Questions Merged

3. Set the Merge Type

1. Select Save File to Docs then Email
2. Allow it to create a new folder to place the saved doc into.
3. Name the Doc “Agenda”
4. Select whether this agenda will be a PDF or Google Doc (I like GD)
5. Use the $email variable in the email address line.
   1. Remember this will merge all the recipients into one line separated by commas.
6. Include a subject line and a short note.
7. Use the drop down menu to determine what kind of doc you are sending…
   1. PDF
   2. Google Doc View only
   3. Google Doc able to edit

I use view only GD so they can make a copy and edit, take notes, etc.

That's it - you are all set!

1. Send out a link to the form and tell participants to complete Part 1 of the form.
2. When you feel everyone has submitted questions on Part 1, ask them to go to the same link, but fill out Part 2.
3. Once everyone has filled out part 2, go to the same link that, and in the Name entry field, enter CloseNow. This will trigger the mail merge and the email...

All participants will receive the Agenda, with the three most highly ranked questions, a list of which participants can help answer what questions, and a list of all questions asked! You are ready to start your training or workshop!