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

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!

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 ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Debbie" <Debbie@discussions.microsoft.com> wrote in message
news:FC396E50-45C2-4D79-AE39-DF1F7BA09F94@microsoft.com...
> 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!

Thank you!!! That worked great. :o)

"Max" wrote:

> 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 ..
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
> "Debbie" <Debbie@discussions.microsoft.com> wrote in message
> news:FC396E50-45C2-4D79-AE39-DF1F7BA09F94@microsoft.com...
> > 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!
>
>
>

You're welcome !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Debbie" <Debbie@discussions.microsoft.com> wrote in message
news:ED102AB7-BCE3-492F-B154-5EA0D71DCBB3@microsoft.com...
> Thank you!!! That worked great. :o)