Concatenate cells when populated

Detrie Zacharias

I am trying to concatenate cells when they are populated and add the column header

This image demonstrates the end result I'm looking for

Something like:

If the cell is not blank

Concatenated field = <Header Text> AND ": - " AND <Cell Text> AND carriage return line feed

AND

If the next column cell is not blank

<Header Text> AND ": - " AND <Cell Text> AND carriage return line feed

AND

If the next column cell is not blank

<Header Text> AND ": - " AND <Cell Text> AND carriage return line feed

If the cell is blank, skip

In the end, I need a clean way for the user to read The Questions and answers when answered and skip when the cell is not populated

Leibel S

@Stavros_McGillicuddy

Below is the syntax you would need for something like this.

=IF([What is your age?]@row <>"", "What is your age?" +": - " + [What is your age?]@row + char(10),"")

For any additional line you want you would add the above but just start it with a '+' symbol. Example:

=IF([What is your age?]@row <>"", "What is your age?" +": - " + [What is your age?]@row + char(10),"")

+IF([What is your sex?]@row <>"", "What is your sex?" +": - " + [What is your sex?]@row + char(10),"")

Detrie Zacharias

@Leibel S

Thank you for this.. It is spot on

The headers will change for each application.. Is it possible to reference the Column by location "Column 4", "Column 5" rather than by the headers "What is your age?", "What is your sex?"?

Leibel S

2 Things:

1. Formula references: If you have this template sheet setup and then change the column name the formula would update the column references (all the @row references).
2. Header text in sheet: To get the correct text in your field you would not be able to get it to show the actual column header, I would suggest using the top row as a reference to the text. your formula would then be:

=IF([What is your age?]@row <>"", INDEX([What is your age?]:[What is your age?],1) +": - " + [What is your age?]@row + char(10),"")

+IF([What is your sex?]@row <>"", INDEX([What is your sex?]:[What is your sex?],1) +": - " + [What is your sex?]@row + char(10),"")

ndontisrinivasa

Can i do the same for a reference group of cell?

I tried this =IF({Moderna Study Details Client Facing Range 2}@row <> 1, "yes", " ")

It says unparseable.