Help with long string!

[JustTheTim]

Hello, I am no expert with excel so please forgive me if what I'm about to ask is silly. I am creating a canned response tool for my department which allows users to pick which options were used. Currently I have it formatted as a list which works but it's not how I'd like it. I would like to put it in paragraph form. The problem I have is the user could choose 1 option or up to 6 options. To have it in proper paragraph form I would need to use "and" and/or commas. I have been unable to find a formula to use that would determine which one is needed based on how many options are chosen. I've included how the list string looks right now. Any suggestions would be appreciated!

"&IF(L17=TRUE,"Representative offered the following solutions:","")&"
"&IF(N17=TRUE,"Option 1","")&""&IF(O17=TRUE," - Customer declined","")&"
"&IF(N18=TRUE,"Option 2","")&""&IF(O18=TRUE," - Customer declined","")&"
"&IF(N19=TRUE,"Option 3","")&""&IF(O19=TRUE," - Customer declined","")&"
"&IF(N20=TRUE,"Option 4","")&""&IF(O20=TRUE," - Customer declined","")&"
"&IF(N21=TRUE,"Option 5","")&""&IF(O21=TRUE," - Customer declined","")&"
"&IF(N22=TRUE,"Option 6","")&""&IF(O22=TRUE," - Customer declined","")&"

 

[Gerald Higgins]

Hi, welcome to the board.

Can you give us maybe two or three different examples of what the results should look like ?

Also, are N17 and O17 (for example) mutually exclusive ?
I.e. will one, and only ever one, of them always be true and the other one false ?
If yes, you might be able to simplify that a bit.

 

[JustTheTim]

All cells listed are independent meaning they could all be true however O17 would not be true if N17 is not true (meaning the user wouldn't select it) and that is the same for N18 and O18, etc. Also, the user could select various options such as option 4 and option 6, they do not have to start with option 1.

An example of what i'm looking for may be (i'm leaving it in list format to make it easier to read).

=IF(L17=TRUE,"Representative offered the following solutions:","")&"
"&IF(N17=TRUE,"Option 1","")&""&IF(O17=TRUE," - Customer declined","")&"
"&IF((N17:N22=TRUE)=2)," and","")&"
"&IF((N17:N22=TRUE)>2),",","")&"
"&IF(N18=TRUE,"Option 2","")&""&IF(O18=TRUE," - Customer declined","")&"
"&IF((N17:N22=TRUE)=2)," and","")&"
"&IF((N17:N22=TRUE)>2),",","")&"
"&IF(N19=TRUE,"Option 3","")&""&IF(O19=TRUE," - Customer declined","")&"
"&IF((N17:N22=TRUE)=2)," and","")&"
"&IF((N17:N22=TRUE)>2),",","")&"
"&IF(N20=TRUE,"Option 4","")&""&IF(O20=TRUE," - Customer declined","")&"
"&IF((N17:N22=TRUE)=2)," and","")&"
"&IF((N17:N22=TRUE)>2),",","")&"
"&IF(N21=TRUE,"Option 5","")&""&IF(O21=TRUE," - Customer declined","")&"
"&IF((N17:N22=TRUE)=2)," and","")&"
"&IF((N17:N22=TRUE)>2),",","")&"
"&IF(N22=TRUE,"Option 6","")&""&IF(O22=TRUE," - Customer declined","")&"

 

[mikerickson]

"Up to six options"
What I would do is construct a number from the options chosen.

OptionButton1 + 2*Optionbutton2 + 4*OptionButton3 + .... + 32*OptionButton6

That will give you a number between 0 and 63 (2^6-1).

That number can then be used to choose the final result. And with that many options, I wouldn't use a single formula.

 

[JustTheTim]

Thank you very much! I'm not quite sure what you mean with "OptionButton1 + 2*Optionbutton2 + 4*OptionButton3 + .... + 32*OptionButton6" but I've been working with option buttons to create six different formula's and I think I'm getting somewhere. So thank you again!

 

[mikerickson]

Represent each option button's state with 1 or 0. 1 if checked, 0 if not.
Then consider the row of option buttons as a binary number e.g. 010001.

The result of the calculation OptionButton1 + 2*Optionbutton2 + 4*OptionButton3 + .... + 32*OptionButton6 will give a unique number for each possible combination of checked/unchecked.