Column Formula Alternative?

BEdgerton

I have people entering information into a sample request sheet using form that allows the user to select multiple types of tests they would like to have run. The form populates "Sheet 1" and I'm using automations to create multiple lines on the destination "Sheet 2" using this solution:

In the Source Sheet:

Count of Rows: The formula depends on how you structured your form. In my case I had a separate "yes/no" question (column) for each situation that needs its own row. For example:

The automation Workflow is just brute force. I created a condition that says "If [Count of Rows] is greater than or equal to 1 then copy the row to the destination", in the same Workflow I added another condition AFTER the first action that says "If [Count of Rows] is greater than or equal to 2 then copy the row to the destination" and so on until I reached a number that was greater than the largest number of rows that I realistically thought could occur. In my case, I used 6. There doesn't appear to be a limit to how many you could have, so it's just a matter of how much time you can put into building the Workflow. Tedious but not hard.

In the Destination Sheet: If the Source sheet is working, then you should get multiple rows in the Destination sheet, like this:

Index: This helper column returns which instance of the duplicate row I am at. The first row of a given Survey ID would be 1, the second would be 2, and so on. It would reset to 1 once you get to the next survey. In my case, I used

=COUNTIF([Survey ID]$1:[Survey ID]@row, [Survey ID]@row)

Note the "$" locking the first row. You could also use a formula comparing the Survey ID to the Survey ID in the row immediately above it and incrementing when the same or resetting to 1 if different.

You will now have a running count for each Survey ID, but you still won't programmatically know which specific response each row represents.

Unique Response: This helper translates the Index (a number) into a more useful piece of data specific to the response you are disaggregating. The way you do this will depend on how you collected the data. In my case, I added a "Header Row" to the Destination Sheet. In the Header Row I placed the value that I wanted to show for each response. Once I did this, I used Index/Collect to return this value for each row:

=INDEX(COLLECT([Response 1]$1:[Response 4]$1, [Response 1]@row:[Response 4]@row, ="Yes"), Index@row)

With this solution, I cannot create column formulas because of the cell references; the user would need to manually copy the the index formula to each cell. Is there another way to do this, through automations or formulas, that would would not need the user to manually copy and paste formulas?

Paul Newcome

Take a look here:

https://community.smartsheet.com/discussion/119380/split-multiple-selections-in-single-form-into-individual-rows-core-product