Merging several form questions into one column
Hi! I have created a form but am having some issues with how fields from the form are collected and reported to employees.
The form is a sign-in for sessions. Attendees select their presenter from several lists (e.g. A - C, D - F). These presenter fields are set up as Contact Lists and linked to our users, and each form can only have ONE presenter selected.
Form responses are then collected in a sheet. Each of the options (e.g. A - C, D - F) has their own column, and those columns are formatted as Contact Lists. I want to combine all the columns into one, and because one presenter can only ever be selected, that column will only ever have one entry.
I tried the =(A - C@row + D - F@row) formula as well as as the =JOIN formula, but the entry column only provides the presenter name as text, not as a contact (see screenshot). Both the source column (A - J) and the destination columns are formatted as Contact Lists and don't allow more than one.
The entries are then captured in a report and filtered based on Current User in the mapped column. But this doesn't work because the column with the formula doesn't result in a Contact, only text.
My question is: Is there a way to have all selections from certain form fields automatically merge into one column? I don't really need the source sheet to have all the different A - C, D - F columns because there will only ever be one selection. I don't see in the form a way to merge columns, and the source sheet is messy with all the different option columns.
If not, how do I get the merge column (the one with the formula) to display the result as a contact?
Answers
-
The formula in the sheet would be along the lines of:
=INDEX(COLLECT([First Contact Column]@row:[Last Contact Column]@row, [First Contact Column]@row:[Last Contact Column]@row, @cell <> ""), 1)
-
Dang it, you're good! That was exactly it! Thank you, Paul - you're always a lifesaver :)
-
Happy to help. 👍️
