How do I combine the contents of multiple cells in one cell?

[Guest]

I am putting together a form in Excel 2003 where several options can be
selected, and I want to combine the information in the selected cells in
another cell with commas seperating the information. For example, I have one
column with environmental hazards listed, one in each row (storage tanks,
batteries, generaters), and in the next column the person filling out the
form can select yes or no if that hazard is present. I want to take the
information from the cells next to the cells that are marked yes, and put it
together, separated with commas, in another cell in a different worksheet .

If someone could please help, I would sure appreciate it!

 

[Max]

If the list of items isn't too many, this formulas play may suffice ..

Sample construct available at:
http://cjoint.com/?mqhAvzNhVX
CombineMultipleCellContentsInOneCell_Debbie_wks.xls

In Sheet: Q,

Assume the list of items is in A2:A10 (a short list), with B2:B10 containing
a data validation* droplist, e.g.:
*created via Data > Validation:
with settings: under "Allow:" List, Source: Yes,No

EnvHazards CheckList
Storage tanks Yes
Batteries No
Generators Yes
etc

Put in C2, copy down to C10:
=IF(A2="","",IF(B2="Yes",SUBSTITUTE(TRIM(A2)," ","-"),""))

Then in Sheet: A,

Put in A2:
=SUBSTITUTE(TRIM(Q!C2&" "&Q!C3&" "&Q!C4&" "&Q!C5&" "&Q!C6&" "&Q!C7&"
"&Q!C8&" "&Q!C9&" "&Q!C10)," ",", ")

A2 will return the desired results**
**any item with > 1 word will appear hyphenated

For the sample above, we'd get in A2:
Storage-tanks, Generators

Adapt to suit ..

 

[Guest]

Thank you!!! That worked great. )

If the list of items isn't too many, this formulas play may suffice ..

Sample construct available at:
http://cjoint.com/?mqhAvzNhVX
CombineMultipleCellContentsInOneCell_Debbie_wks.xls

In Sheet: Q,

Assume the list of items is in A2:A10 (a short list), with B2:B10 containing
a data validation* droplist, e.g.:
*created via Data > Validation:
with settings: under "Allow:" List, Source: Yes,No

EnvHazards CheckList
Storage tanks Yes
Batteries No
Generators Yes
etc

Put in C2, copy down to C10:
=IF(A2="","",IF(B2="Yes",SUBSTITUTE(TRIM(A2)," ","-"),""))

Then in Sheet: A,

Put in A2:
=SUBSTITUTE(TRIM(Q!C2&" "&Q!C3&" "&Q!C4&" "&Q!C5&" "&Q!C6&" "&Q!C7&"
"&Q!C8&" "&Q!C9&" "&Q!C10)," ",", ")

A2 will return the desired results**
**any item with > 1 word will appear hyphenated

For the sample above, we'd get in A2:
Storage-tanks, Generators

Adapt to suit ..

 

[Max]

You're welcome !
Thanks for the feedback ..