How many positions do you anticipate having open at once?

Part of the solution I am working through now involves "pre-filling" columns to account for a maximum number of positions at any one given time. So if you think you will never have need for more than 10 columns based on your screenshots, then we could build in 15 just in case kind of thing.

I think I may have figured something out that doesn't require pre-filling any extras, but I want to make sure the level of effort to manage will work for you.

Whenever a new question is added, it is added to the Questions sheet in the Questions column and then the appropriate box(es) are checked in the position column(s) to indicate which position(s) the question is for. The question row will also need to be indented 1 time so that it is a child of row 2 (the screenshots may help make that a little more clear).

Whenever a new position is added, a new column is added to both sheets. In both sheets the position (column header) is replicated in the first row, and the columns must be inserted "in the middle" of the current range. I added two indicator columns to help keep that part straight.

In the Questions sheet the formula in the second row is dragfilled over from one of the adjacent columns.

In the Form sheet the column will need added to the form.

If you are replacing a column with a new position, then you can just update the column headers and first row in each of the sheets.

Once the form is submitted, the formulas will pull all applicable questions into a Questions column on the form sheet where you can set up an automation to send the data in that column to whomever.

Feel free to ask any questions you need for clarity about the operation and management and take a look at the screenshots. If it seems like something that is easy enough to mange and outputs the desired result, I'll be happy to walk you through the setup. It is actually relatively straightforward with formulas that aren't TOO long and a little bit of conditional formatting.

Screenshots:

Form Sheet:

Questions Sheet:

I didn't want to spend too much time on details if it didn't look like it would work for you, but now that you have confirmed it looks good, here we go...

First we will start with the Questions Sheet:

We are going to insert a helper row at the top where we will manually replicate the positions in accordance with the column headers.

Next we insert another row below that one and in each of the position columns we are going to use:

=JOIN(COLLECT(CHILDREN($Questions@row), CHILDREN(), 1), CHAR(10))

Then we want to indent each of the actual questions below this row 1 time so they are child rows of row 2.

I added 2 "range" columns that help keep things where they need to be for the formulas to work.

For the two "range" columns that indicate where to enter new positions, I used a symbol type column and then selected the fast forward and rewind arrows accordingly. You can use whatever you want for this. I also used conditional formatting to change the cell fill to orange whenever those particular columns have those particular symbols in them. That part is also entirely up to you, but you are definitely going to want those columns to be there.

And that's it for the Questions Sheet.

Now we can go ahead and move on to the Form Sheet:

Insert a helper row at the top and manually enter each of the positions to correspond with the column headers.

You can see which columns were added.

For the two "range" columns I just replicated what I did on the Questions Sheet.

The Categories column (or Positions column) basically just collects which positions were checked off by using:

=JOIN(COLLECT([Add New Positions to the Right --->]$1:[<--- Add New Positions to the Left]$1, [Add New Positions to the Right --->]@row:[<--- Add New Positions to the Left]@row, 1), ", ")

And next the Questions column pulls the questions using this formula (please note the cross sheet reference names indicate exactly what is referenced):

=JOIN(COLLECT({(?'s) Automation Email Attachment Options Row 2}, {(?'s) Automation Email Attachment Options Row 1}, CONTAINS(@cell, Categories@row)), CHAR(10))

Next we go into the form itself and make sure the position columns are all added. You are going to want to add the General column, make it hidden, and use a default value of checked. This is assuming that ALL applicants get the General questions along with the more position specific questions based on the check boxes they select.

And finally we set the automation up so that there are no columns sent using the advanced options. We are going to instead send the data via placeholders just to make sure it all comes through cleanly. So go on down to where you can customize the message and type out what you want it to say, but replace the section where you would list out the questions with a placeholder referencing the Questions column.

Based on my screenshots above, you could use a "custom message" that looks like this in the Automation:

"Manager Name: {{Manager Name}}

Date: {{Date}}

Positions Selected: {{Categories}}

Applicable Questions:

{{Questions}}"

Then the email that is received from the automation would look like this:

"Manager Name: Paul Newcome

Date: 09/02/20

Positions Selected: General, Supervisor, Gap

Applicable Questions:

What is your name?

Have you worked in the Security industry before?

Describe your work history.

Why did you leave your previous job?

What supervisor experience do you have?

How many employees have you supervised?

Why is there a gap in your employment?"

Basically the placeholders will pull the data from the referenced column(s) and insert it into either the subject or the body (wherever you put them) based on whichever row triggered the automation.

Feel free to take your time setting this up and ask any questions you need and then definitely let me know how it feels after you have had a chance to test it.